CODEWITHSUNDEEP
mysqlsql
user3350045
user3350045

Reputation: 33

SQL statement in getting unique rows with conditions

I'm working on a web app and have faced a problem in coming out with an SQL statement that could retrieve,
1) Records that SenderID cannot be UserThree
2) Records that ReceiverID cannot be UserThree
3) Records that does not have UserTwo in the SenderID, as what I wanted to retrieve is records that does not have any "linkage" with UserThree before. In this case, UserTwo is tied to UserThree in Row 2, thus I will need to filter out all records with UserTwo as the SenderID.

What I have is only "UserThree" as the ID to be use in the condition of the SQL statement. Here's the SQL statement I have now

SELECT * FROM user WHERE senderID != "UserThree" AND receiverID != "UserThree";

Which fulfilled the first two condition but not the third.

Below is an example

SenderID       ReceiverID
-------------------------
1)UserOne        UserTwo
2)UserTwo        UserThree
3)UserTwo        UserFour
4)UserTwo        UserFive
5)UserFour       UserSix
6)UserFour       UserSeven

And what I'm trying to get is,

SenderID       ReceiverID
-------------------------
1)UserOne        UserTwo
5)UserFour       UserSix
6)UserFour       UserSever

Upvotes: 0

Views: 62

Answers (1)

redmoon7777
redmoon7777

Reputation: 4526

try:

SELECT * FROM user 
WHERE 
(senderID NOT IN (SELECT senderID FROM user u1 where u1.ReceiverID = "UserThree"))
AND
(ReceiverID NOT IN (SELECT ReceiverID FROM user u2 where u2.senderID = "UserThree"));

with this query all records connected to UserThree (including UserThree as both sender and receiver) will not be included

if you need to only filter connections for senderID use:

SELECT * FROM user 
WHERE 
senderID != "UserThree"
AND
(senderID NOT IN (SELECT senderID FROM user u1 where u1.ReceiverID = "UserThree"));

Upvotes: 1

Related Questions