CODEWITHSUNDEEP
mysqlsqljoinunion
l3est
l3est

Reputation: 417

How to join a table multiple times?

I'm trying to achieve this result, "places" table contains 3 types of places (state, city, slaughter). I want to show them as different columns based on "type" which is an enum column.

Here's what I've tried so far:

SELECT sifs.sif_id, places.`name` as city FROM sifs
INNER JOIN places ON sifs.city_id = places.id
union all
SELECT sifs.sif_id, places.`name` as slaughter FROM sifs
INNER JOIN places ON sifs.slaughter_id = places.id
union all
SELECT sifs.sif_id, places.`name` as state FROM sifs
INNER JOIN places ON sifs.state_id = places.id

here's query result:

sif_id | city
15     | Pedro Juan Caballero(PRY)
15     | FRIGONORTE
15     | Amambay(PRY)

here's what I want:

sif_id | state       | city                     | slaughter
15     | Amambay(PRY)| Pedro Juan Caballero(PRY)| FRIGONORTE

tables fields:

sifs: sif_id, state_id, city_id, slaughter_id places: name, type('state', 'city', 'slaughter')

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I think you want multiple joins:

SELECT s.sif_id, pc.name as city, ps.name as slaughter,
       pst.name as state
FROM sifs s LEFT JOIN
     places pc
     ON s.city_id = pc.id LEFT JOIN
     places ps
     ON s.slaughter_id = ps.id LEFT JOIN
     places pss
     ON s.state_id = pst.id;

This uses LEFT JOIN so all rows in sifs are in the result set, even if some ids have no matches.

Upvotes: 1

Related Questions