Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: Unmatched Records.

    Hi everyone and belated happy new year...

    I think someone has shown me how to do this before but I can't find it.... anyway....

    I have three tables Person, Color and PersonColor.

    Person Table
    Id Name
    -- -----
    P1 Barry
    P2 Jenny

    Color Table
    Id Color
    -- -----
    C1 Black
    C2 Green
    C3 Yellow

    PersonColor Table
    PersonId ColorId
    --- ---
    P1 C2
    P2 C1
    P2 C3

    I need a query to find what colors people have not selected. So the result set would look something like

    P1 Barry C1 Black
    P1 Barry C3 Yellow
    P2 Jenny C2 Green

    I can't use temp tables and I can't use cursors. Anyone got a solution for me?

    Cheers,
    Rokslide
    edit: corrected sample data
    Last edited by rokslide; 01-20-05 at 00:58.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To find colors that were not selected by anyone, I'd use:
    Code:
    SELECT *
       FROM Color AS c
       WHERE NOT EXISTS (SELECT *
          FROM PersonColor AS pc
          WHERE  pc.colorID = c.ID)
    To find the colors that were not chosen by a specific person, I'd use:
    Code:
    SELECT *
       FROM Person AS p
       CROSS JOIN Color AS c
       WHERE NOT EXISTS (SELECT *
          FROM PersonColor AS pc
          WHERE  pc.PersonID = p.ID
             AND pc.ColorID = c.ID)
    Based on your example though, what you seem to want is the people and the colors that they've picked. I'd get that using:
    Code:
    SELECT p.*, c.*
       FROM PersonColor AS pc
       INNER JOIN Person AS p
          ON (p.ID = pc.PersonID)
       INNER JOIN Color AS c
          ON (c.ID = pc.ColorID)
    Interesting question though!

    -PatP

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Quote Originally Posted by Pat Phelan
    Based on your example though, what you seem to want is the people and the colors that they've picked. I'd get that using:
    Code:
    SELECT p.*, c.*
       FROM PersonColor AS pc
       INNER JOIN Person AS p
          ON (p.ID = pc.PersonID)
       INNER JOIN Color AS c
          ON (c.ID = pc.ColorID)
    Interesting question though!

    -PatP
    Hi Pat,

    Thanks for the response. The last answer is closest... I actually want what they haven't picked. Barry hasn't picked Green or Yellow and Jenny hasn't picked Black....

    I'm just thinking..... could you select all possible combinations and then select all those within that select that weren't in the combinations that they had selected?? Something like....

    Code:
    select c.*, p.*, p.Id + "_" +c.Id as 'LinkId' from p Person as P, Color as C where LinkId not in( 
       SELECT p.Id + "_" +c.Id 
       FROM PersonColor AS pc
       INNER JOIN Person AS p
          ON (p.ID = pc.PersonID)
       INNER JOIN Color AS c
          ON (c.ID = pc.ColorID)
       )
    ?????

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think you want example #2 then, and your example data isn't consistent (which is what threw me figuring out what you wanted).

    -PatP

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Doh! You're right, sorry, my bad, I will correct that and give #2 a try. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •