Nested queries in SQL

I am trying to combine 2 queries using a nested query. The first one is this:

SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as start_time
FROM table1 AS de
JOIN table1table2 AS md
ON de.subcode= md.subcode
WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' AND de.end IS NULL AND de.Subcat = 'STOP'
ORDER BY de.time

Which generates the result

Mach  ShortenCode  Tme  
Mach1   451         2017-12-25 08:25
Mach2   854         2017-12-25 08:25

So 451 is on Mach1. The tricky part is that for ShortenCode i would like to show the previous Mach that it was on. It would be something like:

SELECT distinct de.MCH_CODE FROM table1 AS de
join table2 as md
ON de.subcode = md.subcode 
WHERE de.ShortenCode = 'the ones displayed in the first query'

How can I subquery to get:

Mach  ShortenCode  Tme                 Mach(Previous)    
Mach1   451         2017-12-25 08:25   Mach4
Mach2   854         2017-12-25 08:25   Mach5

Basily Mach column has Mach1, Mach2 and also Mach4 and Mach5. I’ve tried this but with no success:

SELECT t1.Mach, t1.ShortenCode, t1.start_time, t2.PreviousMach
    FROM( SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as start_time, mch_
            FROM table1 AS de
            JOIN table1table2 AS md
            ON de.subcode= md.subcode
            WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' AND de.end IS NULL AND de.Subcat = 'STOP'
            ORDER BY de.time
    ) t1
    join
        ( SELECT distinct de.MCH_CODE FROM table1 AS de
                join table2 as md
                ON de.subcode = md.subcode 
                WHERE de.ShortenCode = t1.ShortenCode
        ) t2

If you have any suggestions I would appreciate very much

Answer

I think you can try getting the subquery in the SELECT part like this

SELECT DISTINCT( de.MCH_CODE) AS Mach, md.MAT_CODE as ShortenCode, de.TIME as 
start_time,
(SELECT dee.MCH_CODE FROM table1 AS dee JOIN table1table2 AS mdd
ON dee.subcode= mdd.subcodewhere md.MAT_CODE = mdd.MAT_CODE and 
dee.MCH_CODE < de.MCH_CODE order by dee.MCH_CODE DESC LIMIT 1) as prevMach
FROM table1 AS de
JOIN table1table2 AS md
ON de.subcode= md.subcode
WHERE de.ev = '123' AND de.time > '2017-11-14 07:00' and de.side = 'R' 
AND de.end IS NULL AND de.Subcat = 'STOP'
ORDER BY de.time

although I am guessing that depending on the indexing and the amount of data it could take long to compute.

Advertisement

Answer