58

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.

wdc
  • 2,623
  • 1
  • 28
  • 41
  • Use Group Concat: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field – Yossi Mar 20 '16 at 16:14

5 Answers5

164

= 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
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • For this example, make sure your "multiple row query" returns just one column. Something like SELECT ID FROM FOO will work, whereas SELECT ID, NAME FROM FOO or SELECT * FROM FOO won't. – Steve Saporta Jan 27 '15 at 13:20
  • @SteveSaporta: You are right !! I have included some examples inorder to understand it better. – Raging Bull Jan 27 '15 at 13:22
  • Why not to use LIMIT 0,1 directly in sub query so it gives only one result ? – Vivek Tailor Jul 04 '19 at 08:09
  • @vividusdesigns: You can use limit as well in example 1, as long as the **subquery returns only one value**. I have used max() with no group by, so subquery will return only one value for sure. – Raging Bull Jul 11 '19 at 05:13
  • with **mariaDB 10.3** it keeps throwing the error, if i use the sub query with an `IF` condition, here is the [POST](https://stackoverflow.com/questions/60281253/mysql-using-if-condition-with-a-subquery) , any idea? – Muhammad Omer Aslam Feb 18 '20 at 19:36
14

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 <conditions for source_of_id_table>

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
2

Use MAX in your SELECT query to return one 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 return more than one value

Xupi
  • 92
  • 1
  • 9
John Nico Novero
  • 539
  • 5
  • 10
0

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.

user2288580
  • 2,210
  • 23
  • 16
0

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 = "  
    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
";

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.

David Weisser
  • 117
  • 1
  • 10