Jump to content

Archived

This topic is now archived and is closed to further replies.

Artamir

Complicated query

Recommended Posts

Hi,

 

Let\'s say I have a database which contains phone numbers, dates, a misc. number and some sort of code. Here\'s an example:

 

659-9865 | 13/07/03 | 4 | busy

659-9865 | 13/07/03 | 4 | busy

659-9865 | 13/07/03 | 4 | timeout

 

632-7545 | 15/04/03 | 3 | busy

632-7545 | 15/04/03 | 3 | seconds-based timeout

 

325-8754 | 02/05/03 | 8 | voice

325-8754 | 02/05/03 | 8 | fax

 

326-9854 | 15/04/03 | 3 | busy

326-9854 | 15/04/03 | 3 | busy

326-9854 | 15/04/03 | 3 | busy

326-9854 | 15/04/03 | 3 | seconds-based timeout

 

What I want to do is:

 

1) Select numbers which contain more than one \'status\' (busy, timeout, etc)

 

2) Check if at least one of these is not a busy or seconds-based timeout status.

 

3) If there is one that\'s something else than those two, keep it, and delete the entries with busy and seconds-based timeout.

 

4) If the number contains two or more entries, but all of them are either busy only or seconds-based timeout only, keep one busy and/or one seconds-based timeout. Erase the rest.

 

Here\'s what I would get with the previous example:

 

First group: erase everything except the third line

Second group: keep everything

Third group: keep everything

Fourth group: keep one busy and one seconds-based timeout

 

I have absolutly no idea how to do this, so any help would be appreciated. :)

Share this post


Link to post
Share on other sites

1. Select all entries where they are not like \'busy\' or \'timeout\' ect. This should pick out all the entries with 2 status...

 

SELECT * FROM mytable WHERE status <> \'busy\' OR status <> \'timeout\';

 

2. SELECT * FROM mytable WHERE status <> \'busy\' AND status <> \'seconds-based-timeout\';

Share this post


Link to post
Share on other sites

Thanks, I\'ll try working with that a little. :)

 

Do you have any ideas how I could determine if one of the entries is anything but busy or seconds-based timeout?

Share this post


Link to post
Share on other sites

This should do the trick

 

SELECT * FROM mytable WHERE status <> \'busy\' AND status <> \'seconds-based-timeout\';

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.