CODEWITHSUNDEEP
mysql
Bobr M
Bobr M

Reputation: 3

An sql query for a junction table

I'm wanting to write a query that pulls out a name and address of a specific person.

Names are stored in their own table.

Addresses are storied in their own table.

A junction table stands between them, which has an addressID and a personID.

SELECT name, address
FROM person
JOIN junctionTable ON person.personID = junctionTable.personID
JOIN personAddress ON junctionTable.personID = personAddress.addressID
WHERE junctionTable.personID = 2

This provides a result with the correct person but the wrong address.

This is because in the junction table we have:

addressID = 3 personID = 2

How do I pull the correct person with the correct address? Any guidance would be hugely appreciated, I am very new at this.

Upvotes: 0

Views: 603

Answers (2)

jagath
jagath

Reputation: 238

Try changing the person address table join key:

SELECT name, address
FROM person
JOIN junctionTable ON person.personID = junctionTable.personID
JOIN personAddress ON person.personID = personAddress.personID
WHERE junctionTable.personID = 2

Upvotes: 0

Martin Heralecký
Martin Heralecký

Reputation: 5779

In the second JOIN you need to use addressID on both tables.

SELECT name, address
FROM person
JOIN junctionTable ON person.personID = junctionTable.personID
JOIN personAddress ON junctionTable.addressID = personAddress.addressID
WHERE junctionTable.personID = 2

Upvotes: 1

Related Questions