how to do special join on table A and B (keep A row and add B columns only when there is one on one match)

Question

I want to add to table A informations in table B only for the rows of table A having one and only one row match in table B.

Example : GIVEN table A

+ lastName firstName
+ A        B
+ C        D
+ E        F

AND table B

* lastName firstName age
* A        B         10
* C        D         15
* C        D         20

I WANT TABLE RESULT

* lastName firstName age
* A        B         10
* C        D         null
* E        F         null

Is it possible to do it in SQL? left join is not sufficient

It is possible. Do a GROUP BY on table B before the left join. — jarlh, Jun 12 '19 at 07:51
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Jun 12 '19 at 08:45
Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. What parts are you able to do? Where are you stuck? What did your textbook or the documentation say about anything relevant? (Except--this is a duplicate.) — philipxy, Jun 12 '19 at 08:56
@loic_midy . . . Your description and your data do not match. Why is E/F added into B? What happens to the ages? — Gordon Linoff, Jun 12 '19 at 11:30

score 2 · Answer 1 · answered Jun 12 '19 at 07:57

Kind of

select A.lastName, A.firstName, u.age
from A
left join (
   select lastName, firstName, max(age) age
   from B
   group by lastName, firstName
   having count(*) = 1 -- or may be count(distinct age) = 1
) u on u.lastName = A.lastName and u.firstName = A.firstName

score 0 · Answer 2 · answered Jun 12 '19 at 11:32

I think you want to add unique rows in B to A. For this, I would suggest UNION ALL:

select firstname, lastname, max(age) as age
from b
group by firstname, lastname
having count(*) = 1
union all
select firstname, lastname, null as age
from a
where not exists (select 1
                  from from b
                  group by firstname, lastname
                  having count(*) = 1
                 );

This should, at least, produce the results in your question.

how to do special join on table A and B (keep A row and add B columns only when there is one on one match)

2 Answers2