Solution to subquery returns more than 1 row error

Question

I have one query that returns multiple rows  and another query in which I want to set criteria to be either one of values from those multiple rows   so basicly I want the subquery to look something like this   select    from table where id   multiple row query     Where multiple row query returns multiple rows  So if the values from those rows are 1 2 3 then I want to set id to be 1 or 2 or 3

User · Answer

use MAX in your SELECT to return on value   EXAMPLE  INSERT INTO school year studentid  student id syr id  VALUES   SELECT MAX student id  FROM student    SELECT MAX syr id  FROM school year     instead of   INSERT INTO school year studentid  student id syr id  VALUES   SELECT  student id  FROM student    SELECT  syr id  FROM school year     try it without MAX it will more than one value

User · Answer

Adding my answer  because it elaborates the idea that you can SELECT multiple columns from the table from which you subquery  Here I needed the the most recently cast cote and it s associated information  I first tried simply to SELECT the max votedate  along with vote  itemid  userid etc   but while the query would return the max votedate  it would also return the a random row for the other information  Hard to see among a bunch of 1s and 0s  This worked well   query    quot        SELECT t1 itemid  t1 itemtext  t2 vote  t2 votedate  t2 userid      FROM                   SELECT itemid  itemtext FROM oc item   t1     LEFT JOIN                    SELECT vote  votedate  itemid userid FROM oc votes         WHERE votedate IN           select max votedate  FROM oc votes group by itemid          AND userid  userid  t2     ON  t1 itemid   t2 itemid      order by itemid ASC  quot    The subquery in the WHERE clause WHERE votedate IN  select max votedate  FROM oc votes group by itemid  returns one record - the record with the max vote date

User · Answer

can be used when the subquery returns only 1 value   When subquery returns more than 1 value  you will have to use IN   select    from table where id IN  multiple row query     For example   SELECT   FROM Students WHERE Marks    SELECT MAX Marks  FROM Students    --Subquery returns only 1 value  SELECT   FROM Students WHERE Marks IN         SELECT Marks         FROM Students         ORDER BY Marks DESC        LIMIT 10                        --Subquery returns 10 values

User · Answer

When one gets the error  sub-query returns more than 1 row   the database is actually telling you that there is an unresolvable circular reference  It s a bit like using a spreadsheet and saying cell A1   B1 and then saying B1   A1  This error is typically associated with a scenario where one needs to have a double nested sub-query  I would recommend you look up a thing called a  cross-tab query  this is the type of query one normally needs to solve this problem  It s basically an outer join  left or right  nested inside a sub-query or visa versa  One can also solve this problem with a double join  also considered to be a type of cross-tab query  such as below   CREATE DEFINER  root   localhost  PROCEDURE  SP GET VEHICLES IN       IN P email VARCHAR 150       IN P credentials VARCHAR 150    BEGIN     DECLARE V user id INT 11       SET V user id    SELECT user id FROM users WHERE email   P email AND credentials   P credentials LIMIT 1       SELECT vehicles in vehicle id  vehicles in make id  vehicles in model id  vehicles in model year      vehicles in registration  vehicles in date taken  make make label  model model label     FROM make     LEFT OUTER JOIN vehicles in ON vehicles in make id   make make id     LEFT OUTER JOIN model ON model make id   make make id AND vehicles in model id   model model id     WHERE vehicles in user id   V user id  END   In the code above notice that there are three tables in amongst the SELECT clause and these three tables show up after the FROM clause and after the two LEFT OUTER JOIN clauses  these three tables must be distinct amongst the FROM and LEFT OUTER JOIN clauses to be syntactically correct   It is noteworthy that this is a very important construct to know as a developer especially if you re writing periodical report queries and it s probably the most important skill for any complex cross referencing  so all developers should study these constructs  cross-tab and double join    Another thing I must warn about is  If you are going to use a cross-tab as a part of a working system and not just a periodical report  you must check the record count and reconfigure the join conditions until the minimum records are returned  otherwise large tables and cross-tabs can grind your server to a halt  Hope this helps

User · Answer

You can use in     select    from table where id in  multiple row query    or use a join   select distinct t    from source of id table s join table t on t id   s t id where  lt conditions for source of id table gt    The join is never a worse choice for performance  and depending on the exact situation and the database you re using  can give much better performance

[mysql] Solution to "subquery returns more than 1 row" error

Examples related to mysql