Get friends data in mysql

Question

Hey,

I'm using the following query to get the id and the username of a friend in mysql:

SELECT 
        DISTINCT members.id AS userid, 
        members.gebruikersnaam AS username, 
        members.pasfoto AS avatar,
        members.id AS link, 
    FROM 
        friends
    JOIN 
        members
    ON  
        friends.friend_out = members.id 
    WHERE 
        (
            friends.friend_in = '".mysql_real_escape_string($userid)."'
        OR
            friends.friend_out = '".mysql_real_escape_string($userid)."'
        )

    AND 
        friends.active = 1 
    ORDER BY 
        members.username
    ASC

In the friends table there is one row for each friendship, so the friendship is in both ways.

For example:

friend_in-----friend_out

4-------------6---------

So this is the only row for a friendship between user 4 and 6.

In the query above I want to get the friend data of the logged in user. So I have to check if friend_in is the logged in user (so friend_out have to be returned by the select statement) of friend_out is the logged in user (so friend_in have to be returned by the select statement).

In the query above there has to be something changed in de join to get the friends data out of it.

Can anybody help me with this problem?

Answer 1

The problem is that your friendship relation is non-symmetric and you are joining only to the one end of the friendship but you don't know if that will be the user themself or their friend. Try changing it to a UNION instead of WHERE ... OR ...

SELECT
    members.id AS userid, 
    members.gebruikersnaam AS username, 
    members.pasfoto AS avatar,
    members.id AS link, 
FROM members
JOIN (
    SELECT friends.friend_out AS member_id 
    FROM friends
    WHERE friends.friend_in = '123'
    AND friends.active = 1 
    UNION
    SELECT friends.friend_in AS member_id 
    FROM friends
    WHERE friends.friend_out = '123'
    AND friends.active = 1 
) T1 ON members.id = T1.member_id
ORDER BY members.username