Subquery used as expression doesn't work

Question

I have theses three tables I just created in Postgres:

CREATE TABLE info_clients(id_client INT(pk),name VARCHAR(20), last_name VARCHAR(20));
CREATE TABLE customer_request(id_request INT(pk),client INT(fk),product INT(fk));
CREATE TABLE info_products(id_producto INT(pk),description VARCHAR(20),price INT);

And then I have the next query:

Show the id's of the clientes who bought the 10 most expensive items (using a subquery):

SELECT id_client FROM info_clients
WHERE id_cliente=(  SELECT client 
                    FROM customer_request 
                    WHERE product=( SELECT id_product 
                                    FROM info_products 
                                    ORDER BY price DESC LIMIT 10
                                  )
                 );

But I keep getting the message_ subquery used as an expression returned more than one register, I don't know what am I doing wrong.

The innermost subquery can return up to 10 records ( `limit 10` clause ), but `product` is a scalar (single) value. You can't compare one value with many values using `=` operator, you need a **scalar subquery** here - the scalar subquery can return at most 1 value, not more. Use `IN` operator instead of =, see this tutorial: http://www.w3schools.com/sql/sql_in.asp. — krokodilko, Apr 06 '14 at 16:29
Actually both subqueries could return more than 1 rows. And @kordirko, please don't link to that site of junk. — ypercubeᵀᴹ, Apr 06 '14 at 18:41

score 1 · Answer 1 · edited May 23 '17 at 11:57

What @kordirko commented.
Plus, JOINs are much faster and better in every respect than (nested) IN expressions.

SELECT i.id_client
FROM  (
   SELECT id_product 
   FROM   info_products 
   ORDER  BY price DESC
   LIMIT  10
   ) p
JOIN   customer_request r ON c.product = p.id_product
JOIN   info_clients     i ON i.id_client = c.client;

Aslo, typo? id_cliente <-> id_client
More in the manual on SELECT.

Subquery used as expression doesn't work

1 Answers1