Informix/SQL Question

mengo17mengo17 Posts: 100Member ■■□□□□□□□□
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

Comments

  • bighornsheepbighornsheep Posts: 1,506Member
    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 ='';
    Jack of all trades, master of none
  • PhilippatosPhilippatos Posts: 45Inactive Imported Users ■■□□□□□□□□
    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.
Sign In or Register to comment.