SQL Right join returning blank rows

Question

I am trying to join the below tables:

Table A:

Type1   Type2   ID  Object  Location
Sample  Dummy   1   X111    111222
Sample  Dummy   2   X333    333444
Sample  Dummy   2   X333    555666
Sample  Red     1   X222    666777
Test    Red     1   X222    666777
Test    Red     1   X222    666777

Table B:

Source  Type2   ID  Object  Logged
SR1     Dummy   1   X111    17
SR1     Dummy   2   X333    1
SR1     Red     1   X222    12
SR2     Dummy   1   X111    9
SR2     Dummy   2   X333    9
SR2     Red     1   X222    20

My SQL code:

SELECT B.Source, B.Type2, B.ID, B.Object, B.Logged, A.Location 
FROM A RIGHT JOIN B ON 
    A.Type1='Sample' AND 
    A.Type2=B.Type2 AND 
    A.ID=B.ID AND 
    A.Object=B.Object 
WHERE B.Source='SR2'

I am expecting the following results:

Source  Type2   ID  Object  Logged  Location
SR1     Dummy   1   X111    17      111222
SR1     Dummy   2   X333    1       333444
SR1     Dummy   2   X333    1       555666
SR1     Red     1   X222    12      666777

But I'm getting this instead:

Source  Type2   ID  Object  Logged  Location
SR1     Dummy   1   X111    17      111222
SR1     Dummy   2   X333    1       -
SR1     Dummy   2   X333    1       -
SR1     Red     1   X222    12      666777

What's wrong with my code? Please help.

(1) Tag with the database you are using. (2) Your sample data doesn't have `type1`, so the code should return an error. — Gordon Linoff, May 26 '20 at 15:11
Assuming `'Sample'=B.Type1` is a typo and should reference `A.Type1`, the query as presented does what you're saying it should do. [Rextester demo](https://rextester.com/SXWUO82827). — Eric Brandt, May 26 '20 at 15:20
My apologies. There was a typo in my code, I meant to reference Type1 in the A table. I am using db2. — Cerberus, May 26 '20 at 18:46
[db<>fiddle](https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=1dae87474e871726189c91970caecaad) link with your sample data returns different result. Run the same statement **as is**. Do you get the same result? If not, then what's your db2 version and platform? — Mark Barinstein, May 26 '20 at 21:27
I'm unable to find out the version and the platform. I made a mistake trying to use right join. I wrapped the tables as sub-queries and used left join on them. This gave me the correct output. — Cerberus, May 27 '20 at 12:24

score 0 · Accepted Answer · answered May 26 '20 at 15:13

0

LEFT JOIN is much easier to follow. The issue is the filtering on b.Type1 = 'Sample'. From what I can tell, that is not needed:

SELECT B.Source, B.Type2, B.ID, B.Object, B.Logged, A.Location 
FROM B LEFT JOIN
     A 
     ON A.Type2 = B.Type2 AND 
        A.ID = B.ID AND 
        A.Object = B.Object 
WHERE B.Source = 'SR2';

answered May 26 '20 at 15:13

Gordon Linoff

1,242,037
58
646
786

Apologies, there was a typo. I meant to reference A.Type1. I'm using db2. I will give this a try and if needed, I will do more research. – Cerberus May 26 '20 at 18:48

SQL Right join returning blank rows

1 Answers1