如何防止子查询返回多于1行

Question

The following Subquery returns more than 1 row

SELECT date, 
       order_no, 
       (SELECT product_name 
        FROM   product 
        WHERE  product_id IN (SELECT product_id
                              FROM   order_det)),
       qty 
FROM   order_det 
WHERE  order_no IN (SELECT order_no 
                    FROM   order_id 
                    WHERE  cust_id = (SELECT log_id
                                      FROM   orderpanel_log
                                      ORDER  BY orderpanel_log.date DESC
                                      LIMIT  1)) 

How can I prevent that?

Answer 1

Your subquery

(SELECT product_name 
        FROM   product 
        WHERE  product_id IN (SELECT product_id
                              FROM   order_det ))

could return more then a rows so you should reduce the number of rows retuned eg:

(SELECT product_name 
        FROM   product 
        WHERE  product_id =  (SELECT product_id
                              FROM   order_det
                              limit 1 ))

.

SELECT date, 
       order_no, 
        (SELECT product_name 
        FROM   product 
        WHERE  product_id =  (SELECT product_id
                              FROM   order_det
                              limit 1 ))
       qty 
FROM   order_det 
WHERE  order_no IN (SELECT order_no 
                    FROM   order_id 
                    WHERE  cust_id = (SELECT log_id
                                      FROM   orderpanel_log
                                      ORDER  BY orderpanel_log.date DESC
                                      LIMIT  1)) 

but i suggest you of try refactoring your query using inner join and reduce or avoid the use of subquery

  SELECT od.date, 
         od.order_no, 
         p.product_name,
         od.qty  
  FROM   order_det od 
  INNER JOIN product p on p.product_id = od.product_id
  INNER JOIN order_id oi  on o1.order_no = od.order_no
  WHERE  cust_id = ( 
      SELECT log_id
      FROM   orderpanel_log
      ORDER  BY orderpanel_log.date DESC
       LIMIT  1
    )