SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help with multiple tables and outer join

    I have 5 tables:

    TABLE1
    ID
    t1_name

    TABLE2
    ID
    t2_name

    TABLE3
    ID
    t3_name

    TABLE1_join_TABLE2
    t1_ID
    t2_ID

    TABLE1_join_TABLE3
    t1_ID
    t3_ID


    SELECT DISTINCT t1.t1_name
    FROM table1 t1, table2 t2, table3 t3, TABLE1_join_TABLE2 tj2, TABLE1_join_TABLE3 tj3
    WHERE t1.ID = tj2.t1_ID
    AND t1.ID = tj3.t1_ID
    AND t2.ID = 3

    I have a search form with several options that equate to selections from all the tables. I am construct the query programmatically appending SQL statements if certain search fields are checked or filled in. For example, one field checks for all values in TABLE3 where there is a join in TABLE1.

    What is going wrong is there might not be a record for a particular record in t1.ID, in TABLE2. SO, that record will not be included.

    I think I need some kind of RIGHT or OUTER JOIN.

    Any ideas?

    Many thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, you need some kind of outer join, specifically left outer joins

    but of more concern is that you seem to be relating two unrelated many-to-many relationships

    as a quick exercise, not necessarily in your database but on paper, consider three people: curly, larry, and moe

    now give each of them one or more pets (cat, dog, snake, etc.) and also give each of them one or more cars (ford, chev, vw, etc.)

    now show the result of your 5-way join

    you can't do it without redundancies or duplicates

    you will definitely get the wrong counts if you try to count either pets or cars while joining to both of them

    it's inefficient, and requires you to use DISTINCT when you want something as simple as a list of people who have a certain pet or a certain car

    the table design is correct, but joining all 5 tables in one query is tricky
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have the right idea in that you resolve your M:N relationships by using an intermediate table that is 1:M with the other 2 tables. But you can NEVER join all 3 together, as r937 points out. To go from table1 to table2 you have to use a subquery, possible from ver 4.1

    In the example you give here it is not neccessary

    SELECT t1.name FROM tj3 INNER JOIN t1 ON tj3.t1_id=t1.id
    INNER JOIN tj2 ON tj2.t1_id= t1.id
    WHERE tj2.t2_id=3

    Rename your id columns in each table to the same as you have them when they are foriegn keys - its a hell of a lot easier to follow and avoids accidents. eg t1.t1_id=tj2.t1_id otherwise someday someone will do t1.id=t2.id.


Bookmarks

Posting Permissions

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