NOT IN with 2 columns

Question

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?

Please refer, the possible duplicate http://stackoverflow.com/questions/8435107/mysql-where-not-in-using-two-columns — Maheswaran Ravisankar, Jan 12 '14 at 15:26
The value of `receive` in the sub-query is a constant because of the `where receive = '.UID.'` clause. As far as I can tell, you don't need the `receive` column in the inner select list anyway — , Jan 12 '14 at 15:26

score 5 · Accepted Answer · answered Jan 12 '14 at 15:25

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)

NOT IN with 2 columns

1 Answers1