CODEWITHSUNDEEP
mysqlsqljoin
blackstone8
blackstone8

Reputation: 65

Correct Join Syntax to connect just the rows from Table A that match with Table B

My Goal

I'm trying to do a join on table A and table B.

I want to list out all the people who live at each street in table A. But I don't want to list everyone in table A, only the people who live at each street in table B.

What I've tried

Table A

Fred    123 Fake Street
Bob     456 Any Street
Alice   456 Any Street
Jenny   879 Spring Street
Cassie  879 Spring Street
Spinner 227 Nothing Street
Ranger  227 Nothing Street
Swifty  227 Nothing Street
Loggy   821 Victory Way

Table B

227 Nothing Street
456 Any Street

Query Result (some type of join)

Spinner 227 Nothing Street
Ranger  227 Nothing Street
Swifty  227 Nothing Street
Bob     456 Any Street
Alice   456 Any Street

Upvotes: 0

Views: 106

Answers (2)

Nick
Nick

Reputation: 147166

An INNER JOIN works fine:

SELECT a.*
FROM tableA a
JOIN tableB b ON b.address = a.address

Output:

name    address
Bob     456 Any Street
Alice   456 Any Street
Spinner 227 Nothing Street
Ranger  227 Nothing Street
Swifty  227 Nothing Street

Demo on dbfiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want:

select a.*
from a
where exists (select 1 from b where b.address = a.address);

You can do the same thing with an inner join. I have no idea what you mean by "Inner Join on A and B : returns only the first person at each address". That is simply incorrect.

Upvotes: 1

Related Questions