NULL not showing from right table if LEFT JOIN using.
-
The problem is, There is two tables A and B. Both table has two fields F1 and F2. 96 records in A. A.F2 will be unique. 1 record in B. That one record is matching to A's F2 to B's F2.
The Query is
SELECT A.F1, A.F2, B.F1, B.F2 FROM A LEFT JOIN B ON A.F1 = B.F1
It will show all 96 records from A and repeating B's one record 96 times with this. Actually i need B.F2 should be NULL in the unmatching 95 records.
Even SQL help also describe like this about LEFT JOIN...
------------------------------------------------------------------------
"LEFT JOIN or LEFT OUTER JOIN - The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains NULL values for all select list columns coming from the right table."
------------------------------------------------------------------------
But here it is repeating. what's the problem in my query ? what to do ? I need NULL there. please help me.
Shaiju.
_____________________________________________
One ounce of practice is more important than tonnes of dreams -
Your code is correct... my next step would be a SELECT COUNT(*) on the B table to make sure that I only had one row because it sounds like you have more than 1...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
what's your sqlserver version an sp /hotfisex ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The problem is, There is two tables A and B. Both table has two fields F1 and F2. 96 records in A. A.F2 will be unique. 1 record in B. That one record is matching to A's F2 to B's F2.
The Query is
SELECT A.F1, A.F2, B.F1, B.F2 FROM A LEFT JOIN B ON A.F1 = B.F1
You wrote that F2 is unique, but you joined on F1. Is that really what you wanted to do? Is there only one occurrence of the value of B.F1 in A?
Post some of the data so we can verify the query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply