What am I doing wrong

[cymerman]

Hello ALL,
I want to list every client # that is in the clients table but is not in the contracts table. The purpose is to bring all clients that do not have any contract attached to it and clean the clients table.

clients

client_no surname firstname
1 john smith
2 johny smith1
3 jorge silva

contracts

client_no contract_no price
1 06123101 1000
3 06123001 2000


Is the query below correct ? The result of it does not show any rows (it runs without errors) and I know what the result should be.

SELECT client_no from clients
WHERE client_no NOT IN (select client_no from contracts)

What am I doing wrong ?

THANKS A LOT

 

[PHV]

Some SQL engines prefer this:
SELECT client_no
FROM clients
WHERE NOT (client_no IN (SELECT client_no FROM contracts))

Or this:
SELECT A.client_no
FROM clients A LEFT JOIN contracts B ON A.client_no = B.client_no
WHERE B.client_no IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cymerman]

Thanks for the quick reply.
The result of your first query is "no rows found" (the same result as I am getting with mine) and there is a SINTAX error in your second query. Is not your fault, I forgot to mention that i am using an old version of informix. I believe that is the reason.
Bottom line, Why is the query not retrieving that right answer and saying that there are no rows that satisfy my query ? I really can't understand.

 

[PHV]

Are client_no the same data type in both tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cymerman]

Yes. Integer. And accepts NULL in both too.

 

[PHV]

And this ?
SELECT client_no
FROM clients C
WHERE NOT EXISTS(SELECT * FROM contracts WHERE client_no=C.client_no)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cymerman]

You got it !!!!
THANKS A LOT