List all friends and join the user table

Question

I have a database called "friends" and it has "user_id" and "friend_id". user_id is the invitor and friend_id of the recipient. Please note that when friendship is created, I DO NOT make 2 entries in the database, for example, 1.2 and 2.1.

How to list all my friends, given that my users.user_id may vary between friends.friend_id and friends.user_id in the friends table. Also how to join the query in the "users" table to get the names of all my friends.

Answer 1

One option would be to combine two queries:

select u.name
    from friends f
        inner join users u
            on f.user_id = u.user_id
    where f.friend_id = @YourID
union
select u.name
    from friends f
        inner join users u
            on f.friend_id = u.user_id
    where f.user_id = @YourID

Answer 2

, . - :

$friends_result = mysql_query("
    SELECT
        users.`name` AS name
    FROM
        friends
    LEFT JOIN
        users
    ON
        users.`id` = friends.`user_id`
    OR
        users.`id` = friends.`friend_id`
    AND
        users.`id` != '" . $user_id . "'
    WHERE
        friends.`user_id` = '" . $user_id . "'
    OR
        friends.`friend_id` = '" . $user_id . "'");

echo "<strong>My friends:</strong><br />";

while($friends_array = mysql_fetch_array($friends_result))
{
    echo $friends_array['name'] . "<br />";
}

Answer 3

, , friends user_id, - .
, , .

, , ( ). a >

UNION , Joe said join, OR (, JOIN ... ON u.user_id = friends.user_id OR u.user_id = friends.friend_id) IN (JOIN ... ON u.user_id IN (friends.user_id, friends.friend_id)), ( EXPLAIN ).