Reputation: 1031
I have three MySQL tables: 'people', 'people2id', 'places'. The first one stores information about people, where the 'places' table stores information about their addresses. The middle one, 'people2id', interconnects this two tables (could be that one person has two or more addresses).
Now I want to go to some person's profile and see his profile, but also his associated addresses. I created this query for that:
SELECT * FROM people p
JOIN people2id e ON p.peopleId = e.peopleId
JOIN places a ON a.peopleId = e.peopleId
WHERE p.peopleId = number
This works when the person has associated address(es), otherwise, it will fail. I do not understand if I should use any kind of JOIN or use UNION for this matter.
Upvotes: 1
Views: 74
Reputation: 1
You should use LEFT JOIN
.
SELECT * FROM people p
JOIN people2id e ON p.peopleId = e.peopleId
LEFT JOIN places a ON a.peopleId = e.peopleId
WHERE p.peopleId = number
Upvotes: 0
Reputation: 28247
Change the JOIN
to LEFT JOIN
, i.e.
SELECT * FROM people p
LEFT JOIN people2id e ON p.peopleId = e.peopleId
LEFT JOIN places a ON a.peopleId = e.peopleId
WHERE p.peopleId = number
Using LEFT JOIN
will include all the records from people
, whether they contain associated records or not.
Upvotes: 4
Reputation: 1575
UNION is used for get same kind of informations from two different queries and return them as a single result set
JOIN is used for add columns and data to rows (not as simple but you can see it like this)
Upvotes: 2