How can I left join multiple data from the same mySQL table?

Question

This is my table position:

+----+---------+------+--------+---------+
| id | teacher | cook | doctor | dentist |
+----+---------+------+--------+---------+
|  1 |       3 |    4 |      2 |       1 |
+----+---------+------+--------+---------+

And this is my table people:

+----+-----------+--------+-----+
| id | firstname |  name  | age |
+----+-----------+--------+-----+
|  1 | Fred      | Miller |  42 |
|  2 | Emily     | Rose   |  32 |
|  3 | Ben       | Harper |  38 |
|  4 | Samanta   | Jones  |  35 |
+----+-----------+--------+-----+

My request from the mySQL database

$pdo = $db->query('
SELECT *, position.id AS id, people.id AS people_id 
FROM position 
LEFT JOIN people 
ON position.teacher=people.id;
ON position.cook=people.id;
ON position.doctor=people.id;
ON position.dentist=people.id;
');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
echo "The teacher is "$row['firstname']." ".$row['name'];
echo "The cook is "$row['firstname']." ".$row['name'];
echo "The doctor is "$row['firstname']." ".$row['name'];
echo "The dentist is "$row['firstname']." ".$row['name'];
}

My result is:

The teacher is Ben Harper
The cook is Ben Harper
The doctor is Ben Harper
The dentist is Ben Harper

The result I need:

The teacher is Ben Harper
The cook is Samanta Jones
The doctor is Emiliy Rose
The dentist is Fred Miller

what about changing table structures: 1- change position table to (id, positionName) 2- add extra column to people table position_id .. then you have simpler query — mohsenJsh
– mohsenJsh, Commented Mar 3, 2017 at 16:34

Pramod · Accepted Answer · 2017-03-03 16:45:49Z

It will be better if you restructure your table.

+----+------------+
| id | position   |       
+----+------------|
|  1 |  teacher   |
|  2 |  cook      |
|  3 |  doctor    |
|  4 |  dentist   |
+----+------------+


+----+-----------+--------+-----+-------------+
| id | firstname |  name  | age | position_id |
+----+-----------+--------+-----+-------------+
|  1 | Fred      | Miller |  42 |  4          |
|  2 | Emily     | Rose   |  32 |  3          |
|  3 | Ben       | Harper |  38 |  1          |
|  4 | Samanta   | Jones  |  35 |  2          |
+----+-----------+--------+-----+-------------+

Here you can have a foreign key reference on people.position_id and position.id. This way you can have many people with same position.

SELECT position.id id
     , people.id people_id
     , people.firstname
     , people.name
  FROM position 
  LEFT 
  JOIN people 
    ON people.position_id = position.id;

Rob Wilkins · Accepted Answer · 2017-03-03 16:57:27Z

As noted in comments and answers above, if you can restructure your table, you absolutely should -- as others have mentioned, it's better to restructure it to have that extra column, and then your query will be both simpler and much more efficient. But if you're stuck in a position where you can't (and if you only have a SMALL amount of data as presented, so that the inefficiency of this isn't too much of a concern), then you could use a UNION to glue several simple queries together :

Select position.id, firstname, lastname, 'cook' AS position
FROM position LEFT JOIN people ON position.cook = people.id

UNION

Select position.id, firstname, lastname, 'teacher' AS position
FROM position LEFT JOIN people ON position.teacher = people.id

UNION
...

Again, this is NOT an efficient approach -- you should restructure your data if you can. But if you can't for whatever reason, then this would get you back the data you need in a set of rows that pulls back what you need.

Rahul · Accepted Answer · 2017-03-03 16:33:02Z

0

Try this,

$pdo = $db->query('
SELECT *, position.id AS id, people.id AS people_id,
(case when position.teacher <> '' then 'teacher'
when position.cook <> '' then 'cook'
when position.doctor <> '' then 'doctor' 
when position.dentist <> '' then 'dentist') position_name
FROM position 
LEFT JOIN people 
ON position.teacher=people.id;
ON position.cook=people.id;
ON position.doctor=people.id;
ON position.dentist=people.id;
');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
echo "The ".$row['position_name']." is "$row['firstname']." ".$row['name'];
}

answered Mar 3, 2017 at 16:33

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

Collectives™ on Stack Overflow

How can I left join multiple data from the same mySQL table?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related