CODEWITHSUNDEEP
sqldb2
macunte
macunte

Reputation: 465

Return only one value of Table A where exists in Table B

Find unique records in table A where exist in table B.

Table A

CustID, name, addr1
A001, Bill Adams, 123 main st
B007, Jon Brown, 229 oak st
C029, Sue Cook, 16 park ave

Table B

CustID, invoice_no
A001, 189
A001, 202
A001, 243
C029, 212

Desired results: only 1 instance of A001 from table B and not 3:

A001, Bill Adams
C029, Sue Cook

current sql:

select A.CustID, A.name
from table A
join table B on A.custID = B.custID

Upvotes: 1

Views: 96

Answers (6)

Esperento57
Esperento57

Reputation: 17492

Solution 3 :

select * from tableA f1
where f1.CustID in ( select f2.CustID  from TableB f2)

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

Solution 2 :

select distinct f1.* 
from tableA f1
inner join TableB f2 on f1.CustID=f2.CustID

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

solution 1

select * from tableA f1
inner join lateral
(
 select * from TableB f2
 where f1.CustID=f2.CustID
 fetch first rows only
) f3 on 1=1

Upvotes: 0

macunte
macunte

Reputation: 465

I ended up simply adding 

select distinct A.custID, A.name

as @dnoeth suggested unless there are issues with this approach. Seems to do the trick for me.

Upvotes: 0

Dessma
Dessma

Reputation: 609

The duplicates happen because of the join which is similar to a cartesian product.

If you go with something like this it should work :

SELECT A.CustID, A.name
FROM table A
WHERE EXISTS (SELECT * FROM B WHERE A.custID= B.custID)

Upvotes: 1

Jacob H
Jacob H

Reputation: 2515

Something like this should work:

SELECT A.CustID, A.Name, A.Addr1
FROM Table A
JOIN (SELECT DISTINCT CustID From Table) B ON A.CustID = B.CustID

You could also do WHERE EXISTS instead of a JOIN but my understanding is the JOIN will have better performance.

Upvotes: 1

Related Questions