Outer Join with Where returning Nulls

Hi I have 2 tables. I want to list

all records in table1 which are present in table2

all records in table2 which are not present in table1 with a where condition

Null rows will be returned by table1 in second condition but I am unable to get the query working correctly. It is only returning null rows

SELECT 
    A.CLMSRNO,A.CLMPLANO,A.GENCURRCODE,A.CLMNETLOSSAMT,
    A.CLMLOSSAMT,A.CLMCLAIMPRCLLOSSSHARE
FROM 
    PAKRE.CLMCLMENTRY A 
RIGHT OUTER JOIN (
    SELECT 
        B.CLMSRNO,B.UWADVICETYPE,B.UWADVICENO,B.UWADVPREMCURRCODE,
        B.GENSUBBUSICLASS,B.UWADVICENET,B.UWADVICEKIND,B.UWADVYEAR,
        B.UWADVQTR,B.ISMANUAL,B.UWCLMNOREFNO 
    FROM
        PAKRE.UWADVICE B 
    WHERE
        B.ISMANUAL=1
) r
ON a.CLMSRNO=r.CLMSRNO

ORDER BY 
    A.CLMSRNO DESC;

---

Which OS are you using ?

Table aliases are case sensistive on some platforms, which is why your join condition ON a.CLMSRNO=r.CLMSRNO fails.

Try with A.CLMSRNO=r.CLMSRNO and see if that works

---

我不理解你的第一次尝试，但是这里基本上是你需要的，我想：

SELECT *
FROM TABLE1
INNER JOIN TABLE2
    ON joincondition

UNION ALL

SELECT *
FROM TABLE2
LEFT JOIN TABLE1
    ON joincondition
    AND TABLE1.wherecondition
WHERE TABLE1.somejoincolumn IS NULL

---

我想你可能想要删除子查询并将其列置于主查询中，例如

SELECT A.CLMSRNO, A.CLMPLANO, A.GENCURRCODE, A.CLMNETLOSSAMT, 
       A.CLMLOSSAMT, A.CLMCLAIMPRCLLOSSSHARE,  
       B.CLMSRNO, B.UWADVICETYPE, B.UWADVICENO, B.UWADVPREMCURRCODE, 
       B.GENSUBBUSICLASS, B.UWADVICENET, B.UWADVICEKIND, B.UWADVYEAR, 
       B.UWADVQTR, B.ISMANUAL, B.UWCLMNOREFNO     
  FROM PAKRE.CLMCLMENTRY A
       RIGHT OUTER JOIN PAKRE.UWADVICE B 
          ON A.CLMSRNO = B.CLMSRNO
 WHERE B.ISMANUAL = 1
 ORDER 
    BY A.CLMSRNO DESC;

链接地址: http://www.djcxy.com/p/71102.html

上一篇: INSERT INTO SELECT不起作用

下一篇: 外部加入返回空值的位置