Home » SQL & PL/SQL » SQL & PL/SQL » LEFT JOIN PROBLEM
LEFT JOIN PROBLEM [message #429801] Thu, 05 November 2009 17:56 Go to next message
pc131
Messages: 36
Registered: May 2006
Member
Hi All!

I have 3 tables:

[test1]
id name1
--- ------
1 value1

[test2]
id name2
--- ------
1 value2

[test3]
id name3
--- ---------
3 value3_01
4 value3_02

I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test3.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed.
How to make sql query to display:
id1 name1 name2 name3
1 value1 value2 NULL

I constructed query:
SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3
FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)
ON (t1.id=t2.id and t1.id=t3.id)

but it gives me:

id1 name1 name2 name3
1 value1 NULL NULL

name2 is NULL instead of desired "value2". WHY?

LEFT JOIN DEFINITION:
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.

There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result.

BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed? Confused

Hope anyone can help me. I am in big trouble.

Thanks in advance.
Tom


Re: LEFT JOIN PROBLEM [message #429809 is a reply to message #429801] Thu, 05 November 2009 19:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The problem is that a (straight) JOIN is needed between table1 & table2 & only LEFT JOIN done between table1 & table3
Re: LEFT JOIN PROBLEM [message #429861 is a reply to message #429809] Fri, 06 November 2009 00:45 Go to previous messageGo to next message
pc131
Messages: 36
Registered: May 2006
Member
Hi, thanks for your input.

But when test2.id=2 the result of this query is empty. And I want then the result to be:

id1 name1 name2 name3
1 NULL NULL NULL.

I think I tried INNER JOIN test1 and test2 table, but I can have situations when test2.id doesn't match test1.id. Still I want to display ALL records of test1, but NULLS should be in test2 and test3 columns if no appropriate ids were found (test1.id=1, test2.id=2, test3.ids=3,4.....).

Why LEFT JOIN doesn't display results for table test 2 in which ids/id match id from "left" table test 1, if I join anther one table test3 in which id don't match ids from left table? Then result is: data from "left table" - this is OK, data from test3 table as NULL - this is OK, but data from test2 table should be value2, because test1.id=test2 id?????

Thanks in advance for your help.
Re: LEFT JOIN PROBLEM [message #429873 is a reply to message #429861] Fri, 06 November 2009 01:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
pc131 wrote on Fri, 06 November 2009 07:45
But when test2.id=2 the result of this query is empty. And I want then the result to be:

id1 name1 name2 name3
1 NULL NULL NULL.

I think I tried INNER JOIN test1 and test2 table, but I can have situations when test2.id doesn't match test1.id. Still I want to display ALL records of test1, but NULLS should be in test2 and test3 columns if no appropriate ids were found (test1.id=1, test2.id=2, test3.ids=3,4.....).

In your first post, there was none such requirement. Ok, so try LEFT JOIN between TEST1 and TEST2 only. But in that case, why should NAME1 be NULL? I would suppose it being "value1", as it comes from TEST1.
pc131 wrote on Fri, 06 November 2009 07:45
Why LEFT JOIN doesn't display results for table test 2 in which ids/id match id from "left" table test 1, if I join anther one table test3 in which id don't match ids from left table? Then result is: data from "left table" - this is OK, data from test3 table as NULL - this is OK, but data from test2 table should be value2, because test1.id=test2 id?????

As you are joining TEST2 and TEST3 together, the result of their join (which is empty) is joined. Join TEST2 and TEST3 separately, as already suggested by BlackSwan.
FROM test1 t1
  LEFT JOIN test2 t2 ON (t1.id = t2.id)
  LEFT JOIN test3 t3 ON (t1.id = t3.id)
Re: LEFT JOIN PROBLEM [message #429885 is a reply to message #429801] Fri, 06 November 2009 02:17 Go to previous message
pc131
Messages: 36
Registered: May 2006
Member
Sorry I forgot to mention this requirement that if in table2 id don't match id in table1 value from table2 should be NULL, not that result is all empty.

The query you provided at bottom is OK! Thanks! Tkanks for explaining.

Regards
tom
Previous Topic: Sql query related issue
Next Topic: How to see the structure of a table apart from "desc table_name"?
Goto Forum:
  


Current Time: Sun Dec 04 22:46:43 CST 2016

Total time taken to generate the page: 0.06851 seconds