Nested select in sql error Operand should contain 1 column

Question

i try to execute this query:

select *, (select * from tab1 where tab1.type!="firstype") as P
from tab2 where tab2.attr="something" and tab2.tab1_id=P.id

But i have this error:

Error Code: 1241. Operand should contain 1 column(s)    0,001 sec

i understand the error but not why come out. P.id not work?

You can't use a subquery yielding multiple columns that way. Try selecting only the ID field. — 11684
– 11684, Commented Nov 5, 2014 at 14:58

Ankit Bajpai · Accepted Answer · 2014-11-05 14:59:25Z

1

Change your query to:-

SELECT * 
FROM Tab1 P, Tab2
WHERE tab2.tab1_id=P.id
AND tab2.attr = "something"
AND tab1.type != "firstype"

answered Nov 5, 2014 at 14:59

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DevOps85 Over a year ago

ok but P.id not work, so if i try with: from tab1,tab2 where tab2.tab1_id=tab1.id... works

Gordon Linoff · Accepted Answer · 2014-11-05 14:58:41Z

0

You have the from clause in the wrong place. And, you should use explicit join syntax. Among other things, it will help prevent this type of error:

select *
from tab2 join
     tabl p
     on tab2.tab1_id = P.id and
        tab2.attr = 'something' and
        p.type <> 'firstype';

You also don't need the subquery. The filtering conditions can go in the on or in a where clause on the outer query.

answered Nov 5, 2014 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Nested select in sql error Operand should contain 1 column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related