Josh Luke Blease Josh Luke Blease - 5 months ago 7
SQL Question

MySQL join type for records in the same table

The title is a little awkward, but I will do my best to explain what I'm trying to accomplish.

I have a table called Users and another called Friends.
The abstract structure of the Users table is:

+----+------+----------+----------+
| ID | Name | Username | Password |
+----+------+----------+----------+


The Friends table has an abstract structure like:

+----+--------+----------+--------+
| ID | UserID | FriendID | Hidden |
+----+--------+----------+--------+


Where the UserID is the ID of the user who sent the friend request and the FriendID is the recipient of the request. The hidden column will have a value of 1 if the recipient has chosen to hide the request.

I am wanting to compress this all down to one query, so far I have two separate ones which use either
LEFT JOIN
or
RIGHT JOIN
.

To find sent requests and current friends:

SELECT *, CASE WHEN C.ID IS Null THEN "Request Sent" ELSE "Friends" END AS Status FROM
(SELECT DISTINCT
A.ID, A.Name, E.Hidden
FROM
Users A INNER JOIN Friends E ON A.ID=E.UserID
WHERE
A.ID in (SELECT UserID FROM Friends WHERE FriendID = "1" AND Deleted='No')) C
RIGHT JOIN
(SELECT DISTINCT
B.ID, B.Name, F.Hidden
FROM
Users B INNER JOIN Friends F ON B.ID=F.FriendID
WHERE
B.ID in (SELECT FriendID FROM Friends WHERE UserID = "1" AND Deleted='No')) D
ON C.ID=D.ID


As for friend requests received and current friends:

SELECT *, CASE WHEN D.ID IS Null THEN "Wants to be your friend" ELSE "Friends" END AS Status FROM
(SELECT DISTINCT
A.ID, A.Name, E.Hidden
FROM
Users A INNER JOIN Friends E ON A.ID=E.UserID
WHERE
A.ID in (SELECT UserID FROM Friends WHERE FriendID = "1" AND Deleted='No')) C
LEFT JOIN
(SELECT DISTINCT
B.ID, B.Name, F.Hidden
FROM
Users B INNER JOIN Friends F ON B.ID=F.FriendID
WHERE
B.ID in (SELECT FriendID FROM Friends WHERE UserID = "1" AND Deleted='No')) D
ON C.ID=D.ID


The venn diagram below may be a vital illustrative push if I haven't explained what I need.

Venn Diagram

Please note, for the purpose of constructing the querie(s) I have selected all columns, but I will only require the friends names and ID's at the end.

Answer

Do a union all or union distinct between the two queries this will return all the results in one table.

SELECT *, D.ID, CASE WHEN D.ID IS Null THEN "Wants to be your friend" ELSE "Friends" END AS Status FROM
(SELECT DISTINCT
A.ID, A.Name, E.Hidden
FROM
Users A INNER JOIN Friends E ON A.ID=E.UserID
WHERE
A.ID in (SELECT UserID FROM Friends WHERE FriendID = "1" AND Deleted='No')) C
LEFT JOIN
(SELECT DISTINCT
B.ID, B.Name, F.Hidden
FROM
Users B INNER JOIN Friends F ON B.ID=F.FriendID
WHERE
B.ID in (SELECT FriendID FROM Friends WHERE UserID = "1" AND Deleted='No')) D
ON C.ID=D.ID
union all
SELECT *, D.ID, CASE WHEN C.ID IS Null THEN "Request Sent" ELSE "Friends" END AS Status FROM
(SELECT DISTINCT
A.ID, A.Name, E.Hidden
FROM
Users A INNER JOIN Friends E ON A.ID=E.UserID
WHERE
A.ID in (SELECT UserID FROM Friends WHERE FriendID = "1" AND Deleted='No')) C
RIGHT JOIN
(SELECT DISTINCT
B.ID, B.Name, F.Hidden
FROM
Users B INNER JOIN Friends F ON B.ID=F.FriendID
WHERE
B.ID in (SELECT FriendID FROM Friends WHERE UserID = "1" AND Deleted='No')) D
ON C.ID=D.ID
Comments