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
 General SQL Server Forums
 New to SQL Server Programming
 JOIN IN SQL SERVER 2008R2

Author  Topic 

Nil35
Starting Member

20 Posts

Posted - 2013-07-24 : 10:29:34
I'M USING LEFT JOIN AND ITS ONLY RETURNING MATCHING ROWS FORM BOTH TABLE, I WANT ALL ROWS FROM LEFT TABLE AND ONLY MATCHING ROWS FROM RIGHT TABLE

SELECT A.id, A.CST,A.DST,B.VERSION,B.XYZ FROM T1 A
LEFT JOIN T2 B ON A.ID = B.ID
Where B.VERSION = @VERSION

ANY SUGGETION OR ALTERNATIVE

THANK YOU

nil

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-24 : 10:36:07
non matching rows will have NULL in each column.
your where clause suppresses the results.


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Nil35
Starting Member

20 Posts

Posted - 2013-07-24 : 10:40:12
THANKS BUT
I USED ISNULL(B.XYZ,0)
I HAVE 100 ROWS IN TABLE A AND 50 ROWS IN TABLE B
QUERY SHOULD RETURN ME 100 ROWS BUT IT RETURNING ONLY 50 ROWS

SELECT A.id, A.CST,A.DST,B.VERSION,ISNULL(B.XYZ,0)
FROM T1 A
LEFT JOIN T2 B ON A.ID = B.ID
Where B.VERSION = @VERSION

STILL SAME PROBLEM

nil
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-24 : 10:44:36
your isnull() has to be repeated in the where clause.

btw. is your caps lock key damaged?

posting all in upper case is kind of shouting :)


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Nil35
Starting Member

20 Posts

Posted - 2013-07-24 : 10:48:40
Sorry about That

nil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-24 : 15:50:18
Also, not sure if it matters to you but, by applying that predicate to the subordinate table in the where clause, you have, effectively, turned the left join into an inner join.
Go to Top of Page
   

- Advertisement -