Katti Katti - 7 months ago 14
SQL Question

How to write mysql query to achieve the following type of join (Join based on value of a column )

Table users:

id----email
1-----s@s.com
2-----p@p.com


Table user_buddies

id-----user_id----buddy_id----status enum('accepted','pending')
1------1----------2-----------pending


Suppose I am querying with respect to user 2 then I want to get user 2 email. If I am querying with respect to user 1 then I want to get user 1 email.

The below query is always returning empty rows.

SELECT
users.email,
ub1.*, ub2.*
FROM
users
JOIN user_buddies ub1 ON users.id = ub1.user_id
JOIN user_buddies ub2 ON users.id = ub2.buddy_id
WHERE
users.id = 1;


Can anyone please help me in this?
Thank you.

Expected result:

email-----user_id-----buddy_id-----status
s@s.com --1-----------2------------pending (If queryed on user 2)
p@p.com---1-----------2------------pending (If queried on user 1)


Context:

When
user1
sends request to
user2
only one row is added to the
user_buddies
table with
status=pending
. So, both for
user1
and
user2
buddy list is created using same row. So if queried against
user2
email needed is
user2's
.

Answer

You may check the following query:

SELECT
    users.email,
    ub1.user_id,
    ub1.buddy_id,
    ub1.`status`
FROM
    users
JOIN user_buddies ub1 ON users.id = ub1.user_id OR users.id = ub1.buddy_id
WHERE   users.id = 2;

SQL FIDDLE DEMO

Result:

email      user_id     buddy_id     status
p@p.com       1           2         pending (if queried on users.id = 2)
s@s.com       1           2         pending (if queried on users.id = 1)

EDIT:

SELECT
    (
        SELECT
            u.email
        FROM users u
        WHERE   u.id =
        IF (
            users.id = ub1.user_id,
            ub1.buddy_id,
            ub1.user_id
        )
    ) AS buddyEmail,
    ub1.user_id,
    ub1.buddy_id,
    ub1.`status`
FROM
    users
INNER JOIN user_buddies ub1 ON users.id = ub1.user_id
OR users.id = ub1.buddy_id
WHERE   users.id = 2;