CODEWITHSUNDEEP
mysql
Thomas
Thomas

Reputation: 11

mysql compare tables

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                      |
+--------+--------------+----------------------------------+

Upvotes: 0

Views: 557

Answers (1)

John Parker
John Parker

Reputation: 54445

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.

Upvotes: 2

Related Questions