SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 JOIN IN SQL SERVER 2008R2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nil35
Starting Member

USA
20 Posts

Posted - 07/24/2013 :  10:29:34  Show Profile  Reply with Quote
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

Edited by - Nil35 on 07/24/2013 10:35:05

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/24/2013 :  10:36:07  Show Profile  Visit webfred's Homepage  Reply with Quote
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

USA
20 Posts

Posted - 07/24/2013 :  10:40:12  Show Profile  Reply with Quote
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

Edited by - Nil35 on 07/24/2013 10:44:52
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 07/24/2013 :  10:44:36  Show Profile  Visit webfred's Homepage  Reply with Quote
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

USA
20 Posts

Posted - 07/24/2013 :  10:48:40  Show Profile  Reply with Quote
Sorry about That

nil
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/24/2013 :  15:50:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000