当有记录时，LEFT OUTER JOIN使我在正确的表上为NULL [英] LEFT OUTER JOIN getting me NULLS on 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查询使我在P.ACCOUNT，P.FLD1，P.FLD2，P.FLD3，P.FLD4，P.FLD5，P.FLD6中(无效). (2)当我这样做时[从PITTSBURG P中选择*-这具有与NEWYORK N帐户匹配的所有帐户，并且所有这些帐户都与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 .

非常感谢您的帮助

推荐答案

问题应该出在where子句中，如果记录像您在ACCOUNT字段中所说的那样匹配，则where子句将为空，可能是在您应用了where子句，则NEWYORK表中的结果行在ACCOUNT字段中与PITTSBURG表没有对应的匹配项，测试一一删除where子句并查看结果.

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.

这篇关于当有记录时，LEFT OUTER JOIN使我在正确的表上为NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！