2

I am trying to understand how to join and display data from three tables and have created these three that list person (person), types of fruit (fruit) and the fruit the people like (favs).

 Person               Fruit                 Favs

id  |  name        id  | type        id |  person_id  |  fruit_id

1   |  Peter        1  | apple       1  |  Peter      |  orange
2   |  Sue          2  | orange      2  |  Sue        |  apple
3   |  John         3  | banana      3  |  John       |  banana
4   |  Mary                          4  |  Peter      |  apple
                                     5  |  Sue        |  orange

My aim is to learn how to join all three tables and display which fruits (if any) the people like. Just like this:

Peter  | orange, apple
Sue    | apple, orange
John   | banana
Mary   |

I just about understand how to join all three tables to display the data above but the thing that really confuses me is how to echo out the results. Should I be using nested while loops or a foreeach loop? I've got so confused and would really appreciate someone showing me the way. The closest I've got is this (which is far off I know).

<?php

$sql="SELECT person.name, favs.fruit_id 
      FROM person LEFT JOIN favs
      ON person.name = favs.person_id
      ORDER by person.id";

$result_set=mysql_query($sql);

while($row=mysql_fetch_array($result_set))
{
    echo $row['name'];
    echo $row['fruit_id'];
    echo '<br />';
}
Ivan
  • 34,531
  • 8
  • 55
  • 100
JulianJ
  • 1,259
  • 3
  • 22
  • 52
  • take a look at this http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – Ashkan S Jul 22 '16 at 22:35
  • Your fav table should use the ids of the foreign keys, not their values – Jaime Jul 22 '16 at 23:54

2 Answers2

1

Ok in your table example data, I assume in table Favs you placed the names for readability, wouldn't it be the IDs themselves. Assuming that is the case using the GROUP_CAT your SQL statement would be:

SELECT p.name, GROUP_CONCAT(ft.name)
FROM favs f
INNER JOIN fruit ft ON ft.id = f.fruit_id
LEFT JOIN person p ON p.id = f.person_id
GROUP BY f.person_id
jasonlam604
  • 1,456
  • 2
  • 16
  • 25
  • Well spotted, you are quite right. Your query works (I adjusted:` LEFT JOIN person p ON p.name = f.person_id`. However why is Mary is omitted from the results, she has not `favs` but she should still appear in them? – JulianJ Jul 23 '16 at 09:36
  • 1
    Change the LEFT JOIN to a RIGHT JOIN, note for Mary since there is no data the result will be NULL for Mary – jasonlam604 Jul 24 '16 at 05:17
-1

Just do it like this:

SELECT Person.id,Fruit.name FROM Person INNER JOIN Person.id=Fruit.person_id WHERE ...
Undo
  • 25,519
  • 37
  • 106
  • 129
John
  • 1
  • 1