CODEWITHSUNDEEP
sql
user3129080
user3129080

Reputation:

Find friends users

I have a users table that contains various information about the user (name, first name, last name, .........) and a table friendships that contains three columns:

  1. user_req: username of the requesting user

  2. user_acc: username of the user who accepts or rejects the request for friendship

  3. status: 0 if the request is pending, 1 if user_req and user_acc are friends

    Now I would like to make a query that returns all users (with all their characteristics: name, surname, username, ...) who are friends of a given user username.

I have to make a join between the two tables "friend" and "users" but how?

Upvotes: 0

Views: 93

Answers (2)

Sal00m
Sal00m

Reputation: 2916

You can use this query:

SELECT u.* 
FROM user u
INNER JOIN friendship f ON u.username=f.user_acc
WHERE f.user_acc = ? AND f.status = 1

Upvotes: 1

Magnus Reftel
Magnus Reftel

Reputation: 999

Something like the following should do the trick:

select * from user, friendship where friendship.user_acc=? and friendship.user_req=user.name and friendship.status=1;

Upvotes: 0

Related Questions