CODEWITHSUNDEEP
mysqlsql
Curtis
Curtis

Reputation: 2704

NOT IN with 2 columns

I have a query like so, I understand the syntax ain't correct but how would I perform such "action":

SELECT *
FROM accounts C
JOIN proxies P ON C.proxy_id = P.proxy_id
WHERE C.account_id NOT IN
    (SELECT send,
            receive
     FROM action
     WHERE send = C.account_id
       AND receive = ".UID.") LIMIT 1

I'm shown the following error Operand should contain 1 column(s) because of what I'm aware I'm not supposed to do NOT IN with 2 columns, so how would I do a query like so?

Upvotes: 1

Views: 206

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

another solution would be to work with NOT EXISTS

WHERE NOT EXISTS
      (select null 
       from action 
       where 
        (send = c.account_id or receive = c.account_id)
        and receive = '.UID.')
LIMIT 1

FYI, you can have two values in a NOT IN, but they must be on both sides.

where (x, y) not in (select (a, b) from t)

Upvotes: 5

Related Questions