LEFT OUTER JOIN getting me NULLS on right table when there are records

Question

Below is my left outer join query :

    SELECT  N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4,     P.FLD5, P.FLD6 

FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P  ON   N.ACCOUNT = P.ACCOUNT
WHERE N.FLD1 in ('EC','BP','J1','MP','C1','BP','AD','E1' )
AND N.FLD2 = 'CHICAGO' 
GROUP BY
  N.ACCOUNT, N.FLD1, N.FLD2,  P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6

(1)MY query is getting me (null) in P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6. (2) when I do [select * from PITTSBURG P - this has all the accounts that match with NEWYORK N account and all of them P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6 have data ).

Wondering my query is returning (null) records when there are actually match records in the right table .

Thanks very much for your help

Also, if you could provide an example set from each table that would help a lot! — Easton Bornemeier, Jun 23 '17 at 19:43
Gotta be something funky going on with your `GROUP BY`. Why do you even have a `GROUP BY` when you're not using any aggregate functions? — Dave, Jun 23 '17 at 19:48
N.ACCOUNT is a unique value. Also I rmeoved where clause I still get (null) values in my results — jac, Jun 23 '17 at 20:26
I am using group by to eliminate duplicate records as a result of LEFT JOIN — jac, Jun 23 '17 at 20:34
There are a bunch of comments & guessing answers because you have not given basic info, please read & act on [mcve]. Also you are not clear, eg "this has all the accounts that match with NEWYORK N account". PS Use group by to group. Use distinct for distinct records. PS Different tables based on data values (city name) is a code smell. — philipxy, Jun 25 '17 at 05:30
I have given everything very clear. I finally found answer myself , the ON condition on left outer join, I CASTed both fields to be char, it worked and got me results. The ON condition fields need to be on same type. — jac, Jun 26 '17 at 19:05

score 1 · Answer 1 · answered Jun 23 '17 at 20:18

1

The problem should be in the where clauses, if the records match like you said in the field ACCOUNT, you are getting null for the where clauses, may be when you applied the where clauses, the result rows in the NEWYORK table does't have a corresponding match in the field ACCOUNT with the PITTSBURG table, test to delete one by one the where clauses and see the result.

answered Jun 23 '17 at 20:18

wpp

859
6
8

PITTSBURG table has only account in addition to the remaining 6 fields which are just only for address. PITTSBURG table has nothing to do with where clause I put on NEWYORK table. I am only trying to get address fields (fld1 to fld6) when account matches between the two tables. – jac Jun 23 '17 at 20:37
How can I try whether the basic condition of my query (N.account = P.account) gets me non null values ? – jac Jun 23 '17 at 20:52
Just realized NEWYORK table account is defined as varchar 8, and PITTSBURG table account defined as char 8. Is this the cause why I am getting (null) values, if so how do I rectify it without changing the fields of PITTSBURG table definitions – jac Jun 23 '17 at 20:54
@jac What does google tell you about comparing a char & a varchar for equality in SQL? – philipxy Jun 25 '17 at 05:36
of course this will be the problem, the field char may be have space characters to fill all the size of the account in case tha account doesn't have all characters filled, you can try to use TRIM funcion or something like that in the ON clause of the left join, or make a CAST of the char to vachar. – wpp Jun 27 '17 at 12:10

score 0 · Answer 2 · answered Jun 23 '17 at 19:41

0

I'd play around with the joins available to see if your records really do match, refer to: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

In the event they don't, check the variable type in your columns for mismatches (ex: nvarchar vs int)

answered Jun 23 '17 at 19:41

Duke

163
8

This should be in a comment, as it is not a direct answer to OPs question, merely suggestions to try :) – Easton Bornemeier Jun 23 '17 at 19:42
NEWYORK table account is defined as VARCHAR(8) , PITTSBURGH table account is defined as CHAR(8). Will this cause an issue on the condition N.ACCOUNT = P.ACCOUNT – jac Jun 23 '17 at 20:58

score 0 · Answer 3 · answered Jun 23 '17 at 20:37

1- test the query:

SELECT  N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4,     P.FLD5, P.FLD6
FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P  ON   N.ACCOUNT = P.ACCOUNT

if you have data

2- check FLD2 column have 'CHICAGO' data ? for this query:

SELECT  N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4,     P.FLD5, P.FLD6 
FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P  ON   N.ACCOUNT = P.ACCOUNT
WHERE N.FLD2 = 'CHICAGO'

3- check FLD1 column have any 'EC','BP','J1','MP','C1','BP','AD','E1' data ?

SELECT  N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4,     P.FLD5, P.FLD6 
FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P  ON   N.ACCOUNT = P.ACCOUNT
WHERE N.FLD2 = 'CHICAGO'
AND N.FLD1 in ('EC','BP','J1','MP','C1','BP','AD','E1' )

your first table is NEWYORK and second table is PITTSBURG

you dont have any joined and filtered data. you must check your filters and joins.

SELECT N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6 FROM NEWYORK N LEFT OUTER JOIN PITTSBURG P ON N.ACCOUNT = P.ACCOUNT group by N.ACCOUNT, N.FLD1, N.FLD2, P.ACCOUNT,P.FLD1, P.FLD2, P.FLD3, P.FLD4, P.FLD5, P.FLD6 — jac, Jun 24 '17 at 19:46
The above query gets me (null) in the P fields although there is matching data in them. The only difference I see N.ACCOUNT is var char (8) and P.Account is defined as char(8) — jac, Jun 24 '17 at 19:49

LEFT OUTER JOIN getting me NULLS on right table when there are records

3 Answers3

Linked