Ad:

MS SQL Discussion Thread
Unsolved
Views: 1656

Nov 5th, 2009

LEFT JOIN problem

Expand Post »

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 test.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:

sql Syntax (Toggle Plain Text)
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)
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 advace.
Tom

Similar Threads

Can someone help me my LEFT JOIN? in PHP
Help!howto put the where clauses in left join in PHP
Problem with join? in MySQL
Help on DataList and Repeater Problem in ASP.NET
left join in Visual Basic 4 / 5 / 6

pc131

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2009

Permalink

Nov 6th, 2009

Re: LEFT JOIN problem

MS SQL Syntax (Toggle Plain Text)
CREATE TABLE #test1 (ID int, name1 varchar(255))
INSERT #test1 values (1, 'value1')
 
 
CREATE TABLE #test2 (ID int, name2 varchar(255))
INSERT #test2 values (1, 'value2')
 
CREATE TABLE #test3 (ID int, name3 varchar(255))
INSERT #test3 values (3, 'value3_01')
INSERT #test3 values (4, 'value3_02')
 
SELECT	#test1.ID, name1, name2, name3
  FROM	#test1 inner join #test2 on
	#test1.ID = #test2.ID left join #test3 on
	#test1.ID = #test3.ID 
 
DROP TABLE #test1
DROP TABLE #test2
DROP TABLE #test3
create table #test1 (ID int, name1 varchar(255))
insert #test1 values (1, 'value1')


create table #test2 (ID int, name2 varchar(255))
insert #test2 values (1, 'value2')

create table #test3 (ID int, name3 varchar(255))
insert #test3 values (3, 'value3_01')
insert #test3 values (4, 'value3_02')

select	#test1.ID, name1, name2, name3
  from	#test1 inner join #test2 on
	#test1.ID = #test2.ID left join #test3 on
	#test1.ID = #test3.ID 

drop table #test1
drop table #test2
drop table #test3

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 6th, 2009

Re: LEFT JOIN problem

Click to Expand / Collapse Quote originally posted by huangzhi ...

MS SQL Syntax (Toggle Plain Text)
CREATE TABLE #test1 (ID int, name1 varchar(255))
INSERT #test1 values (1, 'value1')
 
 
CREATE TABLE #test2 (ID int, name2 varchar(255))
INSERT #test2 values (1, 'value2')
 
CREATE TABLE #test3 (ID int, name3 varchar(255))
INSERT #test3 values (3, 'value3_01')
INSERT #test3 values (4, 'value3_02')
 
SELECT	#test1.ID, name1, name2, name3
  FROM	#test1 inner join #test2 on
	#test1.ID = #test2.ID left join #test3 on
	#test1.ID = #test3.ID 
 
DROP TABLE #test1
DROP TABLE #test2
DROP TABLE #test3
create table #test1 (ID int, name1 varchar(255)) insert #test1 values (1, 'value1') create table #test2 (ID int, name2 varchar(255)) insert #test2 values (1, 'value2') create table #test3 (ID int, name3 varchar(255)) insert #test3 values (3, 'value3_01') insert #test3 values (4, 'value3_02') select #test1.ID, name1, name2, name3 from #test1 inner join #test2 on #test1.ID = #test2.ID left join #test3 on #test1.ID = #test3.ID drop table #test1 drop table #test2 drop table #test3

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.

pc131

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Nov 2009

Permalink

Nov 6th, 2009

Re: LEFT JOIN problem

I do not understand why if test2.id = 2 then the result will 1 NULL NULL NULL. if you want that result try below:

sql Syntax (Toggle Plain Text)
SELECT	#test1.ID, case when #test1.id <> isNull(#test2.id,0) then null else name1 end as name1, name2, name3  
  FROM	#test1 left join #test2 on	
	#test1.ID = #test2.ID left join #test3 on
	#test1.ID = #test3.ID
SELECT	#test1.ID, case when #test1.id <> isNull(#test2.id,0) then null else name1 end as name1, name2, name3  
  FROM	#test1 left join #test2 on	
	#test1.ID = #test2.ID left join #test3 on
	#test1.ID = #test3.ID

Last edited by peter_budo; Nov 7th, 2009 at 5:35 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 6th, 2009

Re: LEFT JOIN problem

Well, I think

MS SQL Syntax (Toggle Plain Text)
      SELECT t1.id AS id1, t1.name1, t2.name2, t3.name3
      FROM test1 t1 
      LEFT OUTER JOIN test2 t2 ON t1.id = t2.id
      LEFT OUTER JOIN test3 t3 ON t1.id = t3.id
      SELECT t1.id as id1, t1.name1, t2.name2, t3.name3
      FROM test1 t1 
      LEFT OUTER JOIN test2 t2 ON t1.id = t2.id
      LEFT OUTER JOIN test3 t3 ON t1.id = t3.id

might work correctly...

Celt.Max

Reputation Points: 11

Solved Threads: 4

Newbie Poster

Offline

13 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: How to Create database in SQL Server using Javascript & HTML?

Next Thread in MS SQL Forum Timeline: 'UPDATE'ing table with same value over and over again!

DaniWeb Message

Cancel Changes

User Name
Password