Informix/SQL Question

mengo17

Hello All,

I need some help with Informix/SQL.
I need to create a SQL statement.
There are 2 tables "client" and "contracts".
Client table has client_id and other columns with client info like state, address and etc
Contracts table has client_id, contract_id and other with contract info like price, date and etc.

I want to see wich client_id's do NOT have a contract attached to it.

How do I do this ? The query below is not working for me ! I already searched Google and Forums, but can't get this to work.

client

client_no surname firstname dob
1 A QW 2/14/1988
2 B AS 2/14/1986
3 C ZX 2/14/1984
4 D DS 2/14/1982
5 E RE 2/14/1980
6 F TR 2/14/1978

contract

contract_no client_no price
1000 1 2000
1001 3 4000
1002 5 5000
1003 6 2000

Client_id's 2 and 4 DO NOT have a contract. This is the result I want in my query.


THANKS A LOT

bighornsheep

try a RIGHT JOIN

on SQL SERVER, I think it's called RIGHT OUTER JOIN instead,
but something like this should work:

SELECT T1.client_no
FROM client AS T1 RIGHT JOIN contract AS T2
ON T1.client_no=T2.client_no
WHERE T2.contract_no ='';

Philippatos

Client is the left table, and records without a contract number would contain nulls in the result set. So should be something like:

SELECT t1.*, t2.*
FROM client as t1
LEFT OUTER JOIN contract as t2
ON t1.client_no = t2.client_no
WHERE t2.contract_no is null

And OUTER keyword is part of the ANSI standard BTW.