Jump to content


Photo

Complicated query


  • Please log in to reply
3 replies to this topic

#1 Artamir

Artamir
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 13 July 2003 - 10:21 PM

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. :)

#2 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 14 July 2003 - 08:46 AM

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

#3 Artamir

Artamir
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 July 2003 - 10:28 AM

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?

#4 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 14 July 2003 - 12:07 PM

This should do the trick

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users