SQL - Query - where not in select

Question

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!

What exactly is your use case? If you change operators in a where statement, then it's obvious to get different results, so what are you trying to say by changing !> to >? — Alexander Rühl
– Alexander Rühl, Commented Jun 26, 2014 at 11:12
Basically I want to display all the numbers from the above SQL, WHERE Telephone is not in the following query; SELECT * from Telephone WHERE Acceptancedata > '2014/05/01' — nsilva
– nsilva, Commented Jun 26, 2014 at 11:16

GarethD · Accepted Answer · 2014-06-26 11:15:19Z

1

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

answered Jun 26, 2014 at 11:15

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-06-26 11:16:27Z

0

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.

answered Jun 26, 2014 at 11:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL - Query - where not in select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related