CODEWITHSUNDEEP
mysql
RedactedProfile
RedactedProfile

Reputation: 2808

MySQL: Excluding items in the same table collected in a subquery

What I'm trying to accomplish here didn't seem all too difficult, and in execution I don't see anything I'm doing wrong, but I'm getting some results that should negated from the result set.

I'm writing this on my phone so please excuse me while I write this stuff out by hand:

The table is called "requests", and in the program people can sign up to accept a request. A specific request by the client is to hide from users that have already accepted a request.

Here's the catch 22: A request is based off a specific entity from another table, and one can post multiple requests per base entity. Basically removing duplicates of said entity from the system, and I figured a decent way to handle this is to have a new associative row known as a request which contains a unique id, the account id of the requestor, the entity id of the entity the request is based on, a account id field for the acceptor of that particular row of available requests.

As you can see it starts getting a bit messy in explanation but please keep in mind that this is a pretty late change and can't afford to change the system around at this stage.

So here's the query as I best remember:

SELECT * FROM requests WHERE completed = 0 AND entity_id != ANY (SELECT GROUP_CONCAT(entity_id) WHERE accepted_account_id = '8')

8 is my account ID in the system, there are 8 base entities in the system (all posted from the same account, 7) with 2 of those entities having requests based off of them, the first has 4 requests, and the second has 16 requests. So 20 request rows in the requests table. As a user, I have accepted a single request on both entities in the system. So on two rows in the requests table, RequestID of 1 with Entity ID 1 with Acceptor Account ID of 8 And RequestID of 5 with Entity ID 3 with Acceptor Account ID of 8

while all other rows contain a null field for acceptor_account_id

I am trying to simply negate whole entity_ids from the result set, so as not to mess up the pagination going on in the same query (I'm not interested in separating the query, I'm 150% positive this can all be done in one query)

Please if anyones got any questions, please let me know, as this is urgent :(

Upvotes: 1

Views: 293

Answers (1)

Jan.J
Jan.J

Reputation: 3080

SELECT * FROM requests WHERE completed = 0 AND entity_id NOT IN (SELECT entity_id WHERE accepted_account_id = '8' GROUP BY entity_id)

Upvotes: 1

Related Questions