+ Reply to Thread
Results 1 to 3 of 3
  1. Member
    Join Date
    Jul 2006
    Posts
    99

    Certifications
    A+, Network+, Security+, Linux+, LPIC-1, SFCAR, SFCSR, SFCSE, CEH, B.S. in MIS, M.Sc. in Info Security (NSTISSI No. 4011 - CNSSI No. 4013)
    #1

    Default Informix/SQL Question

    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
    Reply With Quote Quote  

  2. SS
  3. Senior Member bighornsheep's Avatar
    Join Date
    Jul 2006
    Location
    Toronto, Ontario, Canada
    Posts
    1,527

    Certifications
    CCDP, CCNP, CCDA, CCNA, ITIL Foundation, MCSA, MCDST, MCP, A+, Network+
    #2
    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 ='';
    Reply With Quote Quote  

  4. Member
    Join Date
    Oct 2006
    Posts
    48
    #3
    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.
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks