search

Home  >  Q&A  >  body text

How to solve "subquery returns more than 1 row" error

<p>I have a query that returns multiple rows, and another query where I want to set the condition to be a value for any one of these multiple rows, so basically I want the subquery to look like this: </p> <pre class="brush:php;toolbar:false;">select * from table where id= (multi-row query);</pre> <p>Where <code>Multi-row query</code> returns multiple rows. So if the values ​​of these rows are 1, 2, 3, then I want the id to be set to 1 or 2 or 3. </p>
P粉235202573P粉235202573525 days ago571

reply all(2)I'll reply

  • P粉310754094

    P粉3107540942023-08-22 13:21:55

    You can use in():

    select * 
    from table
    where id in (多行查询)

    Or use connection:

    select distinct t.* 
    from source_of_id_table s
    join table t on t.id = s.t_id
    where <source_of_id_table的条件>

    Connections are never a worse choice in terms of performance, and depending on the circumstances and the database you are using, may provide better performance.

    reply
    0
  • P粉386318086

    P粉3863180862023-08-22 09:08:13

    = can be used when a subquery returns only one value.

    When a subquery returns multiple values, you need to use IN:

    select * 
    from table
    where id IN (multiple row query);

    For example:

    SELECT *
    FROM Students
    WHERE Marks = (SELECT MAX(Marks) FROM Students)   --子查询只返回一个值
    
    SELECT *
    FROM Students
    WHERE Marks IN 
          (SELECT Marks 
           FROM Students 
           ORDER BY Marks DESC
           LIMIT 10)                       --子查询返回10个值

    reply
    0
  • Cancelreply