-
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.
-
What is the result you are seeking?
Tamil
-
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 ?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|