CODEWITHSUNDEEP
phpmysqlleft-join
peace_love
peace_love

Reputation: 6461

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

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

Upvotes: 0

Views: 67

Answers (3)

Rob Wilkins
Rob Wilkins

Reputation: 1650

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.

Upvotes: 1

Pramod
Pramod

Reputation: 61

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;

Upvotes: 2

Rahul
Rahul

Reputation: 18557

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'];
}

Upvotes: 0

Related Questions