CODEWITHSUNDEEP
mysqlsqljoinouter-join
Blease
Blease

Reputation: 1420

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.

Upvotes: 1

Views: 208

Answers (1)

Hituptony
Hituptony

Reputation: 2860

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

Upvotes: 1

Related Questions