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.
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 ALEFT JOIN T2 B ON A.ID = B.IDWhere B.VERSION = @VERSIONANY SUGGETION OR ALTERNATIVE THANK YOUnil |
|
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. |
|
|
Nil35
Starting Member
20 Posts |
Posted - 2013-07-24 : 10:40:12
|
THANKS BUTI 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 ROWSSELECT A.id, A.CST,A.DST,B.VERSION,ISNULL(B.XYZ,0)FROM T1 ALEFT JOIN T2 B ON A.ID = B.IDWhere B.VERSION = @VERSIONSTILL SAME PROBLEMnil |
|
|
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. |
|
|
Nil35
Starting Member
20 Posts |
Posted - 2013-07-24 : 10:48:40
|
Sorry about Thatnil |
|
|
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. |
|
|
|
|
|
|
|