SQL Left join avoid duplicates

Question

Let's say this is my Table A:

Col1 Col2 Col3 Col4  Col5
a    b    c    d     e
a    b    c    x     f
b    i    j    l     m
b    i    j    v     t

And my second table B:

Col1 Col2 Col3  Col6
a    b    c     g
a    b    c     s
b    i    j     u
b    i    j     h

Table A and B have common colums ( here Col 1, Col 2, and Col 3) and table B has only unique rows, no duplicates. What I want to have is:

Col1 Col2 Col3 Col4 Col5 Col6
a    b    c    d    e    g 
a    b    c    x    f    null
b    i    j    l    m    u
b    i    j    v    t    null

So the thing to do a is a left join on only first match and all others rows from table B that match should be null/empty. I have tried this query :

SELECT  A.*, B.Col6,
FROM    A
LEFT JOIN   
B
ON 
A.Col1 =  B.Col1
AND A.Col2 = B.Col2
AND A.Col3 = B.Col3

But this gives me duplicates. I also tried with distinct, row_number()b ut still not the expected results. I cannot used subqueries and TOP 1 and limit 1 also does not give the expected result. I have the feeling that it is quite simple but yet still no solution.

Can someone help me?

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Jan 28 '22 at 00:27

score 1 · Answer 1 · answered Jan 27 '22 at 13:58

You can do a lateral join, as in:

select
  x.col1, x.col2, x.col3, x.col4, x.col5,
  case when x.rn = 1 then y.col6 end as col6
from (select *, row_number() 
      over(partition by col1, col2, col3 order by col4) as rn from a) x
left join lateral (
  select * from b where (b.col1, b.col2, b.col3) = (x.col1, x.col2, x.col3) 
  order by col6 limit 1
) y on true

Result:

 col1  col2  col3  col4  col5  col6 
 ----- ----- ----- ----- ----- ---- 
 a     b     c     d     e     g    
 a     b     c     x     f     null 
 b     i     j     l     m     u    
 b     i     j     v     t     null

See running example at DB Fiddle.

Emin Mesic · Answer 2 · 2022-01-27T10:46:18.157

0

Maybe OUTER APPLY can help you:

SELECT  A.*, B.Col6
FROM    A
LEFT OUTER APPLY (SELECT TOP 1 *
                  FROM B WHERE A.Col1 =  B.Col1
                               AND A.Col2 = B.Col2
                               AND A.Col3 = B.Col3) B

It will returns you exactly one matching from left side, if not you will have NULL as you already shown in your example.

This will work on SQL Server.

edited Jan 27 '22 at 10:46

answered Jan 27 '22 at 10:43

Emin Mesic

1,681
2
8
18

1

Product specific answer to a question with no dbms specified. At least tell us which dbms this is for. – jarlh Jan 27 '22 at 10:45
Unfortunately that is not working for me (based on Postgres) – Chris267 Jan 27 '22 at 10:52
@Chris267, add the tag from the beginning the next time you ask a question. Don't waste other people's time writing answers for other dbms's. – jarlh Jan 27 '22 at 12:56
@jarlh yes you're right, sorry for that. Thanks! – Chris267 Jan 27 '22 at 13:35
@Chris267: for Postgres you can replace `left outer apply` with the standard compliant `cross join lateral` – Jan 27 '22 at 14:03

SQL Left join avoid duplicates

2 Answers2