CODEWITHSUNDEEP
sql
newbie
newbie

Reputation: 24635

SQL ERROR: Operand should contain 1 column(s)

I have this query and I get error "Operand should contain 1 column(s)", whats wrong in my query?

SELECT * FROM contact AS b WHERE b.id IN 
(
    SELECT * 
    FROM contact AS e 
    WHERE e.firstname LIKE ? 
    OR e.lastname LIKE ? 
    OR e.email LIKE ? 
    OR e.phone LIKE ? 
    OR e.company LIKE ? 
    OR e.profession LIKE ? 
    OR e.mobile LIKE ?
)

Upvotes: 6

Views: 27432

Answers (5)

shahkalpesh
shahkalpesh

Reputation: 33476

    SELECT * FROM contact AS b WHERE b.id IN (SELECT e.Id FROM contact AS e WHERE e.firstname 
LIKE ? OR e.lastname LIKE ? OR e.email LIKE ? OR e.phone LIKE ? OR e.company LIKE ? OR 
e.profession LIKE ? OR e.mobile LIKE ?)

Instead of SELECT * FROM contact, it should be a column which contains values matching to b.id.

So, it should be SELECT e.Id FROM contact

Upvotes: 1

Konamiman
Konamiman

Reputation: 50273

The second select should be SELECT id insetad of SELECT *.

Upvotes: 7

davek
davek

Reputation: 22915

The IN operator expects a list of values which match whatever you are comparing against: the columnb.id in your case. So replace this

WHERE b.id IN (SELECT *

with this

WHERE b.id IN (SELECT id

Upvotes: 13

KB22
KB22

Reputation: 6969

Your WHERE clause uses IN to determine which values of b.id are relevant for your query. In order to use IN your second query in there must return only one column.

Upvotes: 3

Wael Dalloul
Wael Dalloul

Reputation: 23004

SELECT * FROM

the problem in the above statement, because you are selecting more than one column,

change it to 

SELECT * FROM contact AS b WHERE b.id IN (SELECT e.ID FROM contact AS e WHERE e.firstname 
LIKE ? OR e.lastname LIKE ? OR e.email LIKE ? OR e.phone LIKE ? OR e.company LIKE ? OR 
e.profession LIKE ? OR e.mobile LIKE ?)

Upvotes: 2

Related Questions