Home / Questions / 563988
Accepted
capovawi
Asked: 2022-10-25 04:04:06 +0800 CST 2022-10-25 04:04:06 +0800 CST

Subquery without INNER JOIN

  • 772

I have a customer table (id, name, surname) and an order table (id, quantity, date, customer_id), how can I get the data of the customer who made the cheapest order on a given date? I can't use INNER JOIN, only subqueries. If I use the MAX(amount) in the SELECT I get a figure, but I need it to return all the fields so that I can then compare the customer id.

Edit to put what I'm trying. The code has to be with nested queries, and my mental block has led to this horror:

SELECT *
FROM cliente c
WHERE c.id=(SELECT pedido.id_cliente
            FROM pedido
            WHERE (SELECT MIN(cantidad)
                    FROM pedido
                    WHERE (SELECT *
                            FROM pedido
                            WHERE year(fecha)=1999
                    )
            )
);
mysql

1 Answers

  1. Best Answer

    So first, quickly and running this occurs to me

    select c.* from clientes c, pedidos p where c.id = p.id_cliente and year(p.fecha) = 2022 order by cantidad LIMIT 1;

    Or if you prefer with subquery

    select c.* from clientes c where c.id = (
select p.id_cliente from pedidos p where year(fecha) = 2022  group by id_cliente order by cantidad limit 1);

    UPDATE

    As capovawi correctly points out in his comment, there is a problem with the sentence in case there are several clients with a minimum order.

    The query that I believe would result correct in all scenarios is

    select c.* from clientes c, pedidos p where c.id = p.id_cliente and p.fecha = '2022-01-01' and p.cantidad = 
    (select min(p2.cantidad) from pedidos p2 where p2.fecha = '2022-01-01');
    • 2