CODEWITHSUNDEEP
mysqlsqlsubquery
mariovass
mariovass

Reputation: 187

Nested SELECT SQL Queries Workbench

Hi i have this query but its giving me an error of Operand should contain 1 column(s) not sure why?

Select *, 
(Select *
FROM InstrumentModel
WHERE InstrumentModel.InstrumentModelID=Instrument.InstrumentModelID)
FROM Instrument

Upvotes: 0

Views: 1710

Answers (3)

Divya
Divya

Reputation: 1487

according to your query you wanted to get data from instrument and instrumentModel table and in your case its expecting "from table name " after your select * .when the subselect query runs to get its result its not finding table instrument.InstrumentModelId inorder to fetch result from both the table by matching you can use join .or you can also select perticuler fields by tableName.fieldName and in where condition use your condition.

like :

 select Instrument.x,InstrumentModel.y
 from instrument,instrumentModel
 where instrument.x=instrumentModel.y

Upvotes: 1

peter
peter

Reputation: 15119

When you use subqueries in the column list, they need to return exactly one value. You can read more in the documentation

as a user commented in the documentation, using subqueries like this can ruin your performance:

when the same subquery is used several times, mysql does not use this fact to optimize the query, so be careful not to run into performance problems.

example:

SELECT col0, (SELECT col1 FROM table1 WHERE table1.id = table0.id), (SELECT col2 FROM table1 WHERE table1.id = table0.id) FROM table0 WHERE ...

the join of table0 with table1 is executed once for EACH subquery, leading to very bad performance for this kind of query.

Therefore you should rather join the tables, as described by the other answer.

Upvotes: 0

juergen d
juergen d

Reputation: 204854

You can use a join to select from 2 connected tables

select *
from Instrument i
join InstrumentModel m on m.InstrumentModelID = i.InstrumentModelID

Upvotes: 0

Related Questions