Allen White Allen White - 5 months ago 12
MySQL Question

SELECT query returning own username in results

I am creating a chat application that queries an SQL table on load to display a list of the user's current friends in a datagridview. The issue I'm having is the current user also appears in the friends list.

The layout for the table is:

create table Friendships
(
FriendshipID int IDENTITY(100,1) Primary Key,
ReceiverID int,
SenderID int,
Approved bit,
)


When someone sends a request, a row is created in the table with their ID as the senderID and the person they sent it to as the ReceiverID and it is the opposite if someone sends that person a friend request, and "Approved" is set to 0 until the receiver accepts the friend request. In order to populate the datagridview, I need to query the table and return all records where the current user is either the sender or receiver, and display the person with whom they are friends. Each relationship needs to only be shown once.

One of the queries I've tried was:

SELECT UserID, Username, Status
FROM Arwen_Friendships, Arwen_Users
WHERE ReceiverID = UserID
OR SenderID = UserID
AND ReceiverID = @Current_User_ID
OR SenderID = @Current_User_ID
AND Approved =1


where @Current_User_ID is parameterized using the current logged in user's ID. I have tried all manner of joins, SELECT DISTINCT, and subqueries with no luck. The result of the above query returns two rows: one with the logged in user's information and one with the person who's ID is sender or receiver, and completely ignores the "Approved = 1". It will return rows with "Approved = 0".

Answer

Use Union all

(SELECT U.UserID, U.Username, U.Status from Arwen_Friendships AF INNER JOIN Arwen_Users U ON AF.SenderID = U.UserID WHERE AF.ReceiverID = @Current_User_ID AND AF.Approved = 1)

 UNION ALL 

(SELECT U.UserID, U.Username, U.Status from Arwen_Friendships AF INNER JOIN Arwen_Users U ON AF.ReceiverID = U.UserID WHERE AF.SenderID = @Current_User_ID AND AF.Approved = 1)