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

Question

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

Navigation

#1 by (3 votes)
#2 by (3 votes)
#3 by (0 votes)

3

I have the following 2 tables order and 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 question of all this is that I need you to return every order with the tracking of idstatus higher, that is, I need you to return me:

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 instruction (actually with 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

A tracking I order by id , to get the last one, because by business rule the tracking are increasing their idstatus as they are inserted.

What is wrong or what is missing?

mysql sql

asked by Emiliano Torres 24.01.2017 в 21:20

source

3 answers

3

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

answered by 24.01.2017 в 22:07

0

Your problem is that you are doing LIMIT in JOIN ; with that you just bring a row of tracking instead 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);

answered by 24.01.2017 в 22:12

"(400) Bad Request" when trying to connect with Azure Storage Change colors of the ls command in Linux

score 3 · Accepted Answer

Try changing the query like this. The way you have it, the subquery will only bring you a record of all there is and what you need is a 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