mysql compare tables

Question

I'm stuck with some tables in mysql. Don't really know how to join the info from three tables. Very thankful if anyone could help me. Thanks.

---

This is what I have:

Table1.Users

+----+--------+--------------+
| id | name   | lastname     |
+----+--------+--------------+
| 1  | Peter  | Elk          |
| 2  | Amy    | Lee          |
| 3  | James  | Ride         |
| 4  | Andrea | Thompson     |
+----+--------+--------------+

Table2.Projects

+-----+-------------+
| id  | name        |
+-----+-------------+
| 13  | Lmental     |
| 26  | Comunica    |
| 28  | Ecobalear   |
| 49  | Puigpunyent |
+-----+-------------+

Table3.Users_Projects

+----------+-------------+
| id_users | id_projects |
+----------+-------------+
| 1        | 13          |
| 1        | 28          |
| 2        | 13          |
| 2        | 28          |
| 2        | 49          |
| 3        | 28          |
| 3        | 49          |
| 4        | 49          |
+----------+-------------+

And I would like to print something like this:

+--------+--------------+----------------------------------+
| name   | lastname     | project                          |
+--------+--------------+----------------------------------+
| Peter  | Elk          | Lmental,Ecobalear                |
| Amy    | Lee          | Lmental,Ecobalear, Puigpunyent   |
| James  | Ride         | Ecobalear,Puigounyent            |
| Andrea | Thompson     | Puigpunyent                      |
+--------+--------------+----------------------------------+

Answer 1

Something like...

SELECT Users.name, Users.lastname, Projects.name
FROM (Users, Projects, Users_Projects)
WHERE Users_Projects.id_users=Users.id AND Users_Projects.id_projects=Projects.id
ORDER BY ...

...will output a single user/project per line, which you'll then have to manipulate in your choosen language.

Attempting to perform the concatenation, etc. in SQL is liable to lead to a pretty horrendous query.