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.

Welcome to Stack Overflow. You can format source code with the [Code Sample `{}`](http://i.stack.imgur.com/VYd26.png) toolbar button. I've done it for you this time. Additionally, you won't get too far in the world of relational databases if you avoid the `JOIN` operator family. — Álvaro González, Mar 01 '13 at 13:50

score 2 · Accepted Answer · answered Mar 01 '13 at 13:56

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'
)

score 1 · Answer 2 · answered Mar 01 '13 at 13:50

The reason why you are getting that error is because when using IN, the number of columns that should be returned on subquery must be exactly equal to one. But I rather use JOIN over IN clause.

The equivalent query of this using join:

SELECT  DISTINCT a.userID
FROM    counter a
        INNER JOIN coupon_used b
            ON a.ID = b.user_id
        INNER JOIN accounts c
            ON a.ID = c.user_id
WHERE   b.code ='xyz' AND
        DATE_SUB(CURDATE(), INTERVAL 14 DAY ) <= created

`IN` clause will perform slow in the long run. It's uo to you to decide `:D` — John Woo, Mar 01 '13 at 14:05

Operand should contain 1 column error

2 Answers2