"Friend" (2 tables)

Question

I have two tables like this

Users table id | name ------------- 1 | s1 2 | s2 3 | s3 4 | s4 5 | s5 6 | s6 friends table friendID | user_a | user_b -------------------- 1 | 1 | 2 2 | 3 | 1 3 | 4 | 2 4 | 1 | 3 

I want to run this query: Who is friends with s1?
This is my current request, but it does not work.

 select a.name from users a, friends b where a.id=b.user_b and b.user_a = (select b.user_a from friends where a.name='s1'); 

Answer 1

Here you need to join the user table twice with each user_a and user_b :

Try this query:

 SELECT u.name FROM Users u JOIN friends f ON u.id = f.user_b JOIN Users u1 ON u1.id = f.user_a WHERE u1.name = 's1'; 

Result:

 ╔══════╗ ║ NAME ║ ╠══════╣ ║ s2 ║ ║ s3 ║ ╚══════╝ 

See this SQLFiddle

---

Change In the query (which you tried) you used the external table identifier and the name in the subquery. Therefore, you had to use the identifier and name of the subcategory as follows:

 select a.name from users a, friends b where a.id=b.user_b and b.user_a IN (select id from users where name='s1'); 

See this SQLFiddle

Answer 2

Please try the following:

 SELECT DISTINCT c.name FROM users a, friends b, users c WHERE a.id=b.user_a AND b.user_b=c.id AND a.name='s1'; 

Answer 3

 `SELECT DISTINCT users.name FROM users, friends WHERE (users.id=friends.user_a OR users.id=friends.user_b ) AND (friends.user_a='1' OR friends.user_b='1') AND (users.id!='1')` Result: s2 s3