CODEWITHSUNDEEP
mysqlsqljoin
Manitoba
Manitoba

Reputation: 8702

SQL - Join two tables

I have two tables

Users: (id, name)

Relations: (user_id, relation_id)

User_id and relation_id are both ids from the table users.

What I want is ro recover all users who are friend with a specific user.

And here is my sql command: that doesn't work:

SELECT *
FROM users
NATURAL JOIN relations
WHERE user_id IN (SELECT id FROM users WHERE name='John doe');

Could you help me?

Upvotes: 0

Views: 181

Answers (7)

Jayan Kuttagupthan
Jayan Kuttagupthan

Reputation: 550

To get all the relations a person has , the following query will work..

SELECT * FROM users us JOIN relations re ON us.id = re.relation_id WHERE re.user_id = ( SELECT id FROM users WHERE name = 'John doe' )

Upvotes: 2

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34581

SELECT users.*
FROM relations
INNER JOIN users
  ON relations.relation_id = users.id
WHERE relations.user_id = 12345;

You can also get the id with a subquery, just as you did in your example:

WHERE relations.user_id IN (SELECT id FROM users WHERE name='John doe');

Upvotes: 4

Milan Halada
Milan Halada

Reputation: 1934

Friends are people with the same relation_id?

SELECT a.name FROM users a JOIN relations b on a.id = b.user_id
WHERE b.relation_id in 
(select relation_id from relations where id = 'userid of user you are looking for') 
AND a.id !=  'userid of user you are looking for'

If your logic is different pls tell how it is working

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

SELECT friend.*
FROM users AS friend
  JOIN relations AS r
    ON r.relation_id = friend.id
  JOIN users AS u 
    ON u.id = r.user_id
WHERE u.name = 'John doe' ;

Upvotes: 1

Diego
Diego

Reputation: 36126

isnt this just a matter of querying the relations tables by the userId you are looking for?

select * 
from relations
where  user_id = @IdYouArelookingFor or relation_id = @IdYouArelookingFor

Upvotes: 1

sp00m
sp00m

Reputation: 48807

SELECT user_id
FROM relations re
JOIN users us
ON us.id = re.user_id
WHERE relation_id = (
    SELECT id
    FROM users
    WHERE name = 'John Doe'
)

Side note: you can't use NATURAL JOIN here, 'cause there is no column that have the same name and type in the two tables.

Upvotes: 1

Ankit Sharma
Ankit Sharma

Reputation: 4071

Try this

SELECT * FROM users as a 
JOIN relations as b on a.id = b.user_id 
WHERE b.user_id IN (SELECT id FROM users WHERE name='John doe')

Upvotes: 1

Related Questions