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

Question

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

Simply add `DISTINCT` to your Select or switch to `EXISTS` – dnoeth May 02 '17 at 20:20 — dnoeth, May 02 '17 at 20:20

score 1 · Answer 1 · answered May 02 '17 at 20:16

1

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.

answered May 02 '17 at 20:16

Jacob H

Performance will vary depending on the size of B table and the cost of the DISTINCT. Personally I prefer to use EXISTS since it is much easier to read (it does precisely what it says it does!) Also, more on the question of performance here : http://stackoverflow.com/questions/2177346/can-an-inner-join-offer-better-performance-than-exists – Dessma May 02 '17 at 20:24

score 1 · Answer 2 · answered May 02 '17 at 20:19

1

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)

answered May 02 '17 at 20:19

Dessma

score 0 · Answer 3 · answered May 02 '17 at 20:47

0

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.

answered May 02 '17 at 20:47

macunte

Should work fine until you need to add additional columns or joins. If that never happens... no problem! – Jacob H May 02 '17 at 20:49

score 0 · Answer 4 · answered May 03 '17 at 16:56

0

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

answered May 03 '17 at 16:56

Esperento57

score 0 · Answer 5 · answered May 03 '17 at 16:57

0

Solution 2 :

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

answered May 03 '17 at 16:57

Esperento57

score 0 · Answer 6 · answered May 03 '17 at 16:59

0

Solution 3 :

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

answered May 03 '17 at 16:59

Esperento57

6 Answers6