CODEWITHSUNDEEP
mysqlsqljoin
Remy
Remy

Reputation: 59

Looking for information on how to construct an SQL query with multiple mappings

I have a primary table with two additional linked tables, each relationship has a mapping table, I've obfuscated this and simplified the scenario.

Person
id
name
Location
id
name
Person_Location
person_id
location_id
Nationality
id
name
Person_Nationality
person_id
nationality_id

I'm looking to query each person and return one row for each related table

Person Name, Location Name, Nationilty Name

Some of the related tables may not be populated and I'd like the row returned but an empty value representing where the data is missing.

I've got a query with INNER JOINS for Person, Location and Person_Location mapping table but when I add additional joins for the Nationality mapping table, this returns many more rows than exists? I'm happy with a single set of joins but repeating this for additional one is not bearing fruit. I can't seem to find any examples of how to do this although I may not be using the correct approach.

SELECT p.name, lo.name as location, na.name as nationality
FROM person p
INNER JOIN person_location pl ON p.id = pl.person_id
INNER JOIN location lo ON pl.location_id = lo.id
INNER JOIN person_nationality pn ON p.id = pn.person_id
INNER JOIN nationality na ON pm.nationality_id = na.id

Upvotes: 0

Views: 50

Answers (1)

The Impaler
The Impaler

Reputation: 48800

You can use two scalar subqueries to get the result you want. For example:

select p.name,
  (select l.name from location l join person_location pl 
     on pl.location_id = l.id and pl.person_id = p.id limit 1) as location,
  (select n.name from nationality n join person_nationality pn 
     on pn.nationality_id = n.id and pn.person_id = p.id limit 1) as nationality  
from person p

Upvotes: 1

Related Questions