CODEWITHSUNDEEP
sqlsql-server-2008
nsilva
nsilva

Reputation: 5612

SQL - Query - where not in select

Okay here is my SQL:-

SELECT Telephone, 
       Houseno, 
       Street, 
       Tostreet, 
       Passengername, 
       Acceptancedate 
FROM   Telephone 
WHERE  Acceptancedate !> '2014/05/01' 
       AND Telephone LIKE '011%' 
       AND ( Zoneno = '6' 
              OR Zoneno = '5' 
              OR Zoneno = '9' 
              OR Zoneno = '108' ) 
       AND Telephone = 'XXX' 
ORDER  BY Acceptancedate

This displays 5 records for this phone number.

However, if I change the !> to >

it shows 3 records.

The SQL I have is correct, but I only want to display the numbers IF this telephone number doesn't appear when AcceptanceDate > '2014/05/01' (as the phone numbers are not unique)

Any help would be much appreciated!

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You should write the where clause like this:

WHERE  Acceptancedate <= '2014-05-01' AND
       Telephone LIKE '011%' AND
       Zoneno IN ('6', '5', '9', '108') AND
       Telephone = 'XXX'

This will not change the outcome, but here are the changes:

  • !< is not a standard comparison operator. Use <= which is clearer.
  • I changed the format of the date to the ISO standard YYYY-MM-DD format
  • I changed the comparison of ZoneNos to use IN rather than OR

Also, I note two comparisons on Telephone. The first is redundant.

Upvotes: 0

GarethD
GarethD

Reputation: 69769

You could use NOT EXISTS to exclude the telephone numbers that appear after a certain date

SELECT  Telephone, 
        Houseno, 
        Street, 
        Tostreet, 
        Passengername, 
        Acceptancedate 
FROM    Telephone AS t
WHERE   Telephone LIKE '011%' 
AND     Zoneno IN ('6', '5', '9', '108')
AND     Telephone = 'XXX' 
AND     NOT EXISTS
        (   SELECT  1
            FROM    Telephone AS t2
            WHERE   t2.Telephone = t.Telephone
            AND     t2.Acceptancedate > '20140501' 
        );

Upvotes: 1

Related Questions