CODEWITHSUNDEEP
sqlms-accessjoin
xGen
xGen

Reputation: 554

Return null for second duplicate row in JOIN in Access

I saw few posts for avoiding duplicates. But this is not about removing duplicates from the output. but keeping duplicates from one table but avoiding rows repeating from the joining table.

So I have 2 table Table1 and Table2 . Table1 has duplicate values for ID column. Table2 does not have duplicate values for ID column. I wish to join both tables. In the result I want all rows from Table1 including duplicates but i do not want Table2 to repeat rows for each duplicate row in Table1. 

Table1
ID    Column2
1     A
1     B
2     C
3     D

Table2
ID    Column3
1     X
2     Y

My Query is

Select A.ID,A.Column2,B.ID,B.Column3 from Table1 A LEFT JOIN Table2 B on A.ID=B.ID

The result i get is 

ID Column2 ID Column3
1  A       1  X
1  B       1  X
2  C       2  Y
3  D       null null

The expected result is 

ID Column2 ID Column3
1  A       1  X
1  B       null  null
2  C       2  Y
3  D       null null

i could not see any possibilities in access database .

Please let me know if there is any possibility of getting this result

Upvotes: 1

Views: 128

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I can not check right now, but you will get the idea:

select t11.id, t11.col2, t2.id, t2.col3
from (table1 t11
left join (select id, min(col2) as col2 from table1 group by id) t12
  on t11.id = t12.id and t11.col2 = t12.col2)
left join table2 t2 on t12.id = t2.id

Upvotes: 2

Related Questions