Home / Questions / 45810
Accepted
Emiliano Torres
Asked: 2020-01-25 12:20:36 +0800 CST 2020-01-25 12:20:36 +0800 CST

Query with LEFT JOIN and LIMIT 1 in the JOIN does not work for me

  • 772

I have the following 2 tables orderand tracking. The structures are the following:

Order( columns and data ):

id fecha       nroorder description
1  2017-22-01  1000001  Order1
2  2017-22-01  1000002  Order2
3  2017-22-01  1000003  Order3

Tracking( columns and data ):

id idorder idstatus
1  1       1
2  1       2
3  1       3
4  2       1
5  2       2
6  3       1
7  3       2

The point to all this is that I need it to return each orderwith the trackinghighest idstatus, that is, I need it to return:

order.id order.fecha order.nroorder order.description tracking.idstatus
1        2017-22-01  1000001        Order1            3
2        2017-22-01  1000002        Order2            2
3        2017-22-01  1000003        Order3            2

I tried the following statement (actually several but this is the closest one):

SELECT `order`.*, d.*
 FROM `order` left JOIN (SELECT tracking.* 
         FROM tracking
        ORDER BY tracking.id DESC limit 1) d
ON `order`.`id` = d.idorder

I trackingorder it by id, to remove the last one, because by business rule trackingthey increase their idstatusas they are inserted.

What is wrong or what am I missing?

mysql

3 Answers

  1. Best Answer

    Try changing the query like so. The way you have it, the subquery is only going to bring you one record of all that there is and what you need is one record for each order.

    SELECT `order`.*, d.idstatus
FROM `order` LEFT JOIN (SELECT tracking.idorder, MAX(tracking.idstatus) As idstatus  
         FROM tracking
        GROUP BY tracking.idorder) d
ON `order`.`id` = d.idorder
    • 3

  2. A subquery could solve your question:

    SELECT O.*, 
(SELECT tracking.idstatus 
        FROM tracking T
        WHERE T.idorder = O.id
        ORDER BY tracking.id DESC limit 1)
FROM `order` O
    • 3

  3. Your problem is that you are doing the LIMITin the JOIN; with that you only get one row of trackinginstead of one for each row of order.

    This query does what you need:

    SELECT
    `order`.id,
    `order`.fecha,
    `order`.nroorder,
    `order`.description,
    tracking.idstatus

FROM `order`
LEFT JOIN tracking
    ON tracking.id = (
        SELECT id
        FROM tracking
        WHERE tracking.idorder = `order`.id
        ORDER BY tracking.id DESC
        LIMIT 1);
    • 0