Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: Two table specific join

    Hello everyone

    I have two tables in my application:

    table_X
    +-------+
    | a_id | b_id |
    +-------+
    | 1 | 2 |
    | 1 | 3 |
    | 2 | 2 |
    | 3 | 3 |
    | 4 | 1 |
    | 4 | 2 |
    | 4 | 3 |

    table_Z
    +-----------------+
    | b_id | system_flag |
    +----------------+
    | 1 | 0 |
    | 2 | 1 |
    | 3 | 0 |
    | 4 | 1 |

    In this two tables all table_X records have one or more specific b_id's from another table (table_Z). b_id's in table_Z could be system or not system. What I need is a query that would list all a_id's that does not have any b_id's with system flag. (If a_id has even one b_id that is sytem, it should be not returned).

    How can I achieve this ?

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: Two table specific join

    Code:
    SELECT a_id FROM table_X
    JOIN table_Y USING(b_id)
    GROUP BY a_id HAVING SUM(system_flag)=0;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aus, that's pretty cool

    it also works with LEFT OUTER JOIN, in case there's a row in table_X which has no rows in table_Z -- since SUM() ignores nulls, it will work too!

    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    Good point, I do tend to forget about some records not showing up in the result set. I guess that if the tables were created without referential integrity enforced, the LEFT JOIN would be required.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    SELECT a_id FROM table_X
    JOIN table_Y USING(b_id)
    GROUP BY a_id HAVING SUM(system_flag)=0;

    Bingo!

    Thank you very much

Posting Permissions

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