join many tables on one col.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: join many tables on one col.

  1. #1
    Join Date
    May 2004
    Posts
    14

    join many tables on one col.

    Hi,

    I have 5 tables named (alias) a,b,c,d,e respectively.

    Each table has two cloumns, one common column named "bin_id" which has the same value "A10001" in all tables and then each table has another column named "fname","lname","firstadd","newadd","toodd" (for tables a,b,c,d,e respectively).

    When I join the tables on the bin_id, i get the following result:

    SQL> select fname,lname,firstadd,newadd,toodd
    2 from test a,test2 b,test3 c,test4 d,test5 e
    3 where a.bin_id=b.bin_id
    4 and c.bin_id=b.bin_id
    5 and d.bin_id=b.bin_id
    6 and e.bin_id=b.bin_id;

    FNAME LNAME FIRSTADD NEWADD TOODD
    -------------------- -------------------- -------------------- -------------------- --------------------
    Larson Sweat Ajax XXXX AAAA

    However when I add a condition a.fname <> 'Larson':

    SQL> select fname,lname,firstadd,newadd,toodd
    2 from test a,test2 b,test3 c,test4 d,test5 e
    3 where a.bin_id=b.bin_id
    4 and c.bin_id=b.bin_id
    5 and d.bin_id=b.bin_id
    6 and e.bin_id=b.bin_id;
    7 and a.fname <> 'Larson';

    I get no results:

    FNAME LNAME FIRSTADD NEWADD TOODD
    -------------------- -------------------- -------------------- -------------------- --------------------

    which is obviously not the result i was seeking.

    How can i make this multiple join work when I add a condition such as above, Do i need to make left outer joins ?

    Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    What is the result you are seeking?

    Tamil

  3. #3
    Join Date
    May 2004
    Posts
    14
    When i add the condition, " and a.fname <> 'Larson' ", it applies to the value in the "fname" column of table "a" , the join should still fetch the values in the other tables since the value of the join key (bin_id=A10001) is the same in all tables.

    How should I get the values from the other tables ?

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Your first query produced one record, in which a.fname = 'Larson'. Then in your second query you excluded records with a.fname = 'Larson'. That leaves no records. How else could it work?

    You are right, this would take an outer join, so either

    Code:
    SELECT fname,lname,firstadd,newadd,toodd
    FROM   test a,test2 b,test3 c,test4 d,test5 e
    WHERE  a.bin_id(+) = b.bin_id
    AND    c.bin_id = b.bin_id
    AND    d.bin_id = b.bin_id
    AND    e.bin_id = b.bin_id;
    or the ANSI style equivalent:
    Code:
    SELECT fname,lname,firstadd,newadd,toodd
    FROM   test b
           LEFT OUTER JOIN test2 a
           ON   a.bin_id = b.bin_id
           JOIN test3 c
           ON   c.bin_id = b.bin_id
           JOIN test4 d
           ON   d.bin_id = b.bin_id
           JOIN test5 e
           ON   e.bin_id = b.bin_id;

Posting Permissions

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



Click Here to Expand Forum to Full Width