CODEWITHSUNDEEP
mysqlsqldatabase-designjoinunion
Bob Dem
Bob Dem

Reputation: 1031

Confused about using UNION or JOIN

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

Answers (3)

mariushe
mariushe

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

Ryan
Ryan

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

56ka
56ka

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

Related Questions