+ 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