question about how to modify a join statement

bitt3n

Senior member
Dec 27, 2004
202
0
76
I want to have the user view comments made by users he has indicated as friends, and who themselves have indicated that this user is their friend (ie, reciprocal permission).

I have two tables, a users table that contains a user_id and the comment, and a friends table that contains two columns. In each row of the friends table, the first entry is the user_id of the user designating the friendship (the "fan") and the second entry is the friend so designated (the "star").

The join command below will return rows for those users that the user has designated as a friend.

$query = "SELECT * FROM users, friends WHERE fan_id={$_SESSION['user_id']} AND star_id=user_id ORDER BY last_siting_time DESC";

I want to modify this statement so that it does not return rows for users who have not themselves designated the user himself as a friend. In plain english, I want to add to the end of the JOIN the requirement "AND for a given star_id, there must be a row in friends in which star_id is listed as the fan_id value and {$_SESSION['user_id']} is listed as the star_id value."

Perhaps someone might suggest how to modify this statement to do what I want. Thanks!
 

MrChad

Lifer
Aug 22, 2001
13,507
3
81
Try this:

SELECT *
FROM USERS, FRIENDS
WHERE FRIENDS.fan_id = {$_SESSION['user_id']}
AND FRIENDS.star_id = USERS.user_id
AND EXISTS (SELECT 1 FROM FRIENDS SUB WHERE SUB.star_id = {$_SESSION['user_id']} AND SUB.fan_id = FRIENDS.star_id)
ORDER BY last_siting_time DESC;
 

bitt3n

Senior member
Dec 27, 2004
202
0
76
thanks!

someone else suggested a self join:

$query = "SELECT * FROM users u1, friends f1, friends f2 WHERE f1.fan_id={$_SESSION['user_id']} AND f2.star_id = {$_SESSION['user_id']} AND f2.fan_id = f1.star_id AND u1.user_id = f1.star_id ORDER BY last_siting_time DESC";

is there any reason to prefer one or the other of these methods?