Missing rows after join

Database
Enthusiast

Missing rows after join


I have Table 1 , Table 2 , Table A as below 

Table A

ID_NUM

7643458

9050876

Table 1

ID_NUM        ORGN                  Money

7643458      9468976            0.08

7643458      9469313            0.15

Table 2

ID_NUM     VAL         ORGN

9050876    29050.6     9469094

9050876   4696.89     9375065

7643458    873.32       9468976

Expected Output

ID_NUM        ORGN         Money      VAL

7643458      9468976    0.08        873.32

7643458      9469313    0.15         ? 

9050876    9469094      ?             29050.6     

9050876    9375065      ?             4696.89     

Actual output     

ID_NUM        ORGN         Money      VAL

7643458      9468976    0.08        873.32

7643458      9469313    0.15         ? 


I am able to see that the mistake is on the T1.ORGN=T2.ORGN join condition but it missed out the data for the 9050876 ID . 

How to resolve this ? Meaning , I need the VAL of the ID_NUM even if there is no Money from this ID_NUM. 

Tags (1)
3 REPLIES
Enthusiast

Re: Missing rows after join

JOIN SQL is 

SELECT .... 

FROM A 

LEFT JOIN T1 ON A.ID_NUM = T1.ID_NUM 

LEFT JOIN T2 ON A.ID_NUM=T1.ID_NUM 

AND T1.ORGN=T2.ORGN 

Enthusiast

Re: Missing rows after join

Any one, need help on this ....

Junior Contributor

Re: Missing rows after join

Well, the result is correct based on your join conditions

FROM A 
LEFT JOIN T1 ON A.ID_NUM = T1.ID_NUM -- same join condition twice?
LEFT JOIN T2 ON A.ID_NUM=T1.ID_NUM -- same join condition twice?
AND T1.ORGN=T2.ORGN

Without knowing the Primary/Foreign Keys it's hard to tell what you want/need.

Seems like a join of T1/T2 and the a join to A:

SELECT A.ID_NUM, dt.ORGN, dt.Money, dt.VAL
FROM A
LEFT JOIN
(
SELECT
COALESCE(T1.ID_NUM, t2.ID_NUM) AS ID_NUM
,COALESCE(t1.ORGN, t2.ORGN) AS ORGN
,VAL
,Money
FROM T1
FULL JOIN T2
ON t1.ID_NUM=T2.ID_NUM
AND T1.ORGN=T2.ORGN
) AS dt
ON A.ID_NUM = dt.ID_NUM