All Forums Database
nerdgirl 10 posts Joined 05/15
22 May 2015
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. 

 

 

Cheers

nerdgirl 10 posts Joined 05/15
22 May 2015

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 
 
 

Cheers

nerdgirl 10 posts Joined 05/15
22 May 2015

Any one, need help on this ....

Cheers

dnoeth 4628 posts Joined 11/04
23 May 2015

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 

 

Dieter

You must sign in to leave a comment.