Vera Perrone Vera Perrone -4 years ago 85
MySQL Question

How to select from this many-to-many tables

Database: mySQL

What I am looking for is: A way to select a list of users that has status X with User X. Let's say my name is James and I want to get my friends list. Is this possible?

Tabel User:

-- ID

-- first_name

-- last_name

...


Tabel User_Status:

-- User_ID1

-- User_ID2

-- Status


The result I want should look something like this: Let's say my user ID = 0.


User_ID1-----User_ID2-----Status

------0---------------1-----------friends

------0---------------2-----------friends

------0---------------3-----------friends

------0---------------4-----------friends

------0---------------5-----------friends

Answer Source
mysql> select * from User;
+----+------------+-----------+
| ID | first_name | last_name |
+----+------------+-----------+
|  1 | Nikolai    | Tesla     |
|  2 | Albert     | Einstein  |
|  3 | Bertrand   | Russell   |
|  4 | Carl       | Sagan     |
+----+------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from User_Status;                                                                    +----------+----------+---------------+
| User_ID1 | User_ID2 | Status        |
+----------+----------+---------------+
|        1 |        2 | FRIENDS       |
|        1 |        3 | FRIENDS       |
|        2 |        1 | FRIENDS       |
|        3 |        4 | ACQUAINTANCES |
|        3 |        1 | FRIENDS       |
+----------+----------+---------------+

mysql> SELECT u.* 
       FROM User u   
       JOIN User_Status us ON u.ID = us.User_ID1 
       WHERE us.Status = "FRIENDS" AND us.User_ID2 = 1;
+----+------------+-----------+
| ID | first_name | last_name |
+----+------------+-----------+
|  2 | Albert     | Einstein  |
|  3 | Bertrand   | Russell   |
+----+------------+-----------+
2 rows in set (0.00 sec)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download