CODEWITHSUNDEEP
mysqlsqlselectsubqueryinner-join
Ramon
Ramon

Reputation: 410

Subquery on MySQL

I know that may be a repeated question, but I cannot make my mind about it and cannot find the right example.

I'm trying to make a subquery selecting elements with an id x, y or z from a table and, from that selection, the elements with an area xx, yy or zz.

That'd be the query:

SELECT * FROM establecimientos 
INNER JOIN subtipos ON (establecimientos.subtipos_idsubtipo = subtipos.idsubtipo) 
WHERE subtipos_idsubtipo = 11  OR  subtipos_idsubtipo = 12  OR  subtipos_idsubtipo = 13  OR  subtipos_idsubtipo = 14 
AND zonas_idzona in 
(SELECT * FROM establecimientos 
WHERE zonas_idzona = 2  OR zonas_idzona = 3  OR zonas_idzona = 4  OR zonas_idzona = 5  OR zonas_idzona = 6  OR zonas_idzona = 7  OR zonas_idzona = 8)
LIMIT 0, 90

But I get the error only a single result allowed for a SELECT that is part of an expression

I'm in a big headache! Thanks!

Upvotes: 0

Views: 35

Answers (2)

Marc B
Marc B

Reputation: 360692

It's

... zonas IN (SELECT * FROM ...)
                     ^---here

The subquery is returning MULTIPLE fields for every result row, which means the DB has absolutely NO idea which of those fields it should use to compare with zonas.

Change it to 

... zonas IN (SELECT specific_field FROM ...)
                      ^^^^^^^^^^^^^^---changed

instead.

Upvotes: 1

Lucas Desouza
Lucas Desouza

Reputation: 72

You need to update the sub-query to only pull the field you will be using.

Change this:

SELECT * FROM establecimientos 
WHERE zonas_idzona = 2  OR zonas_idzona = 3  OR zonas_idzona = 4  OR zonas_idzona = 5  OR zonas_idzona = 6  OR zonas_idzona = 7  OR zonas_idzona = 8

to:

SELECT <Field Name> FROM establecimientos 
WHERE zonas_idzona = 2  OR zonas_idzona = 3  OR zonas_idzona = 4  OR zonas_idzona = 5  OR zonas_idzona = 6  OR zonas_idzona = 7  OR zonas_idzona = 8

Upvotes: 1

Related Questions