Wednesday 5 September 2018

Mysql LEFT OUTER JOIN get NULL on the right table when there are records

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

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.

0 comments:

Post a Comment