Operand should contain 1 column error

Question

I have following query

SELECT * 
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM  `counter` 
    WHERE DATE_SUB( CURDATE( ) , INTERVAL 14 DAY ) <= created
)
AND id IN (
    SELECT user_id
    FROM coupon_used
)
AND id IN (
    SELECT user_id
    FROM accounts
)

I need to put where clause in coupon_used table that If user id IN coupon useed table where code ='xyz'. I did like this

id IN (
    SELECT user_id, code 
    FROM coupon_used WHERE code ='xyz'
)

but show me an error that:

Operand should contain 1 Column.

Answer 1

A join would be better, but for the minimum changes to what you've written you can use the fact that the subquery doesn't need the column in its where clause to be in the select. i.e. instead of

id IN (
    SELECT user_id, code 
    FROM coupon_used WHERE code ='xyz'
)

you can use

id IN (
    SELECT user_id
    FROM coupon_used WHERE code ='xyz'
)