LEFT JOIN problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT JOIN problem

Author

Topic

pc131
Starting Member

2 Posts

Posted - 2009-11-05 : 18:59:08

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? :?

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

Thanks in advance.
Tom

singularity
Posting Yak Master

153 Posts

Posted - 2009-11-05 : 19:36:59

[code]
SELECT t1.id as id1, t1.name1, t2.name2, t3.name3
FROM test1 t1
LEFT JOIN test2 t2 ON t1.id=t2.id
LEFT JOIN test3 t3 ON t1.id=t3.id
[/code]

pc131
Starting Member

2 Posts

Posted - 2009-11-06 : 01:51:00

Thanks singularity! Thank you so much. It works as I want to.

If exists test2.id in test1 result is

1 value2 NULL

If not exists test2.id in test1 result is

1 NULL NULL

Before with my query in 2nd situation result was empty.

What was wrong in my query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-06 : 01:56:59

quote:
FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)

You have done an INNER JOIN with test3 and test2.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources