CODEWITHSUNDEEP
mysqlsql
user3434701
user3434701

Reputation: 29

Excluding some results from SQL Query

I am working on a project that is sort of like FB. So I have friends (that are stored in "Requests" by having a request between 2 users with a "Response" of 1. I also have "Circles" that are like friend groups. What I want is a SELECT that returns all friends not in a given circle. I believe the Query is correct up until the "AND NOT" at which point I want to exclude all requests (friends) that have either the sender or reciever (I know it is spelled wrong) of the Response who are already contained in the circle.

SELECT us.Username ,
       ur.Username
FROM Requests r 
Join Users    us ON us.UserID = r.Sender 
JOIN Users    ur ON ur.UserID = r.Reciever 
WHERE (    Sender   = ( SELECT UserID
                        FROM Users
                        WHERE Username=?
                      ) 
        OR Reciever = ( SELECT UserID
                        FROM Users
                        WHERE Username = ?
                      )
      ) 
  AND Response=1 
  AND NOT ON ( SELECT un.Username
               FROM CircleMembers c 
               Join Users         un ON un.UserID = c.UserID 
               WHERE CircleID = ( SELECT CircleID
                                  FROM Circles WHERE Name = ?
                                )

Thanks!

Upvotes: 0

Views: 75

Answers (2)

PinnyM
PinnyM

Reputation: 35533

This query should work:

SELECT us.Username, ur.Username 
FROM Requests r 
Join Users us ON us.UserID = r.Sender 
JOIN Users ur ON ur.UserID = r.Reciever
WHERE (us.Username = ? OR ur.Username = ?)
  AND r.Response = 1
  AND NOT EXISTS (
    SELECT 1 FROM Circles c
    JOIN CircleMembers cm ON c.CircleID = cm.CircleID
    WHERE c.Name = ?
    AND cm.UserID IN (us.UserID, ur.UserID)
  )

Upvotes: 3

Marc
Marc

Reputation: 992

Try:

AND column_name NOT IN (SELECT column_name FROM table_name WHERE...)

Upvotes: 0

Related Questions