Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Feb 2002
    Posts
    43

    Unanswered: Query Help

    I am using mysql as my database and I am trying to design two queries to get the following information.

    Table1
    ID
    Name

    Table2
    ID (Foreign Key to Table1)
    Other
    Other
    Other

    I want to get all records from table 1 where ID is not in table2. If the ID is in table2 then I do not want that record.

    The second Query is along the same lines, but requires the use of three tables.

    Table1
    ID
    Name

    Table2
    ID (Foreign Key to Table1)
    Position
    Salaray

    Table3
    ID (Foreign Key to Table1)
    Other
    Other
    Other

    In this query I need to get all records from table 1 where ID is in Table2 and not in Table3.

    Please remember I am using mysql which does not support sub queries. Any help would be appreciative.

  2. #2
    Join Date
    May 2003
    Posts
    87
    Code:
    1. all records from table1 where ID is not in table2
    
    select a.*
    from table1 a LEFT OUTER JOIN table2 b
    ON (a.id = b.id and b.id is null);
    
    2. all records from table1 where ID is in Table2 and not in Table3
    select a.*
    from table1 a JOIN table2 b ON (a.id = b.id)
    LEFT OUTER JOIN table3 c ON (a.id = c.id and c.id is null);
    Hope this helps!

  3. #3
    Join Date
    Feb 2002
    Posts
    43
    Originally posted by dbmadcap
    Code:
    1. all records from table1 where ID is not in table2
    
    select a.*
    from table1 a LEFT OUTER JOIN table2 b
    ON (a.id = b.id and b.id is null);
    
    2. all records from table1 where ID is in Table2 and not in Table3
    select a.*
    from table1 a JOIN table2 b ON (a.id = b.id)
    LEFT OUTER JOIN table3 c ON (a.id = c.id and c.id is null);
    Hope this helps!
    Unfortunatly that did not work. Thanks for the help. Anyone have any other ideas.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dbmadcap, you were very close, however, the null tests belong in the WHERE clause, not the join condition

    select table1.id, table1.name
    from table1
    left outer
    join table2
    on table1.id = table2.id
    where table2.id is null

    select table1.id, table1.name
    from table1
    inner
    join table2
    on table1.id = table2.id
    left outer
    join table3
    on table1.id = table3.id
    where table3.id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2002
    Posts
    43
    Yes that was it thank you. Thnak you to everyone for their help.

Posting Permissions

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