Complicated query

[Artamir]

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.

[rhysmeister]

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\';

[Artamir]

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?

[rhysmeister]

This should do the trick

 

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