CODEWITHSUNDEEP
mysqlsqljoingreatest-n-per-group
Baldrick
Baldrick

Reputation: 11002

MySQL Join on Subquery

I am trying to make a join on a subquery although I can't get the results I require. Can someone either suggest a better query to run or perhaps I shouldn't use a subquery (I am not well versed in any variant of SQL), is there a better query type to run ?

I can see with my limited SQL knowledge that the subquery isn't in the best place. The goal here is to query for fields from tblResults and also join them to the last row in tblTraceOutput that has the same resultid (there are multiple rows in tblTraceOutput that have the same resultid value, so just the last row).

SELECT r.`resultid`, r.`successful`, r.`result`, r.`testdate`, r.`changed`, 
t.`delay1`, t.`delay2`, t.`delay3` 
FROM `tblResults` AS r 
JOIN (
    SELECT `resultid`, `delay1`, `delay2`, `delay3` 
    FROM `tblTraceOutput` 
    WHERE `traceid`='48'
    ORDER BY `outputid` DESC LIMIT 0,1
) AS t ON (t.`resultid` = r.`resultid`) 
WHERE r.`traceid` = '48' ORDER BY r.`resultid` DESC LIMIT 0,20

If I change that JOIN to a LEFT JOIN I will get more results back from my query but with 'NULL' written in the three columns for t.delay1, t.delay2 and t.delay3, in every row except the first. The results pulled from the subquery are only joint to the first row of output from the main query on tblResults. How can I have this subquery run and joint for every row of output in the outer query?

In my head I imagine the following, but I can't make it work in any way:

SELECT r.`resultid`, r.`successful`, r.`result`, r.`testdate`, r.`changed` 
FROM `tblResults` AS r 
(
    SELECT t.`resultid`, t.`delay1`, t.`delay2`, t.`delay3` 
    FROM `tblTraceOutput` 
    WHERE `traceid`='48'
    ORDER BY `outputid` DESC LIMIT 0,1
) AS t
JOIN ON (t.`resultid` = r.`resultid`) 
WHERE r.`traceid` = '48' ORDER BY r.`resultid` DESC LIMIT 0,20

Upvotes: 1

Views: 5300

Answers (3)

John Woo
John Woo

Reputation: 263703

I think this is the one you are looking. The query uses subquery to separately gets the latest outputid for every resultid and resultid on table tblTraceOutput. The result of the subquery is then joined back with table tblTraceOutput (itself) provided that it matches on all column on the subquery. The possible record that will match is the latest one.

SELECT  a.*, b.*
FROM    tblResults a
        INNER JOIN tblTraceOutput b
            ON a.resultid = b.resultid
        INNER JOIN
        (
            SELECT  resultid, traceid, MAX(outputid) max_ID
            FROM    tblTraceOutput
            GROUP   BY resultid, traceid
        ) c ON  b.resultid = c.resultid AND
                b.traceid = c.traceid
                b.outputid = c.max_ID
WHERE   a.traceid = 48
ORDER   BY resultid
LIMIT   0, 20

Upvotes: 1

user1936123
user1936123

Reputation: 216

You can use a LEFT JOIN, just include an expression in the WHERE clause to avoid displaying records where there is no associated record in the joined table:

SELECT r.`resultid`, r.`successful`, r.`result`, r.`testdate`, r.`changed`, 
t.`delay1`, t.`delay2`, t.`delay3` 
FROM `tblResults` AS r
LEFT JOIN `tblTraceOutput` AS t ON (t.`resultid` = r.`resultid`)
WHERE r.`traceid` = '48' AND t.`resultid` IS NOT NULL
ORDER BY r.`resultid` DESC
LIMIT 0, 20;

Upvotes: 1

John Ennis
John Ennis

Reputation: 441

SELECT r.`resultid`, r.`successful`, r.`result`, r.`testdate`, r.`changed` 
FROM `tblResults` AS r 
LEFT JOIN `tblTraceOutput` as t ON (t.`resultid` = r.`resultid`) 
WHERE r.`traceid` = '48' ORDER BY r.`resultid` DESC LIMIT 0,20

left join will return results from tblTraceOutput table even if there are none that match (the returned data is NULL)

inner join will only return results from resultid and tblTraceOutput that match the ON clause

Upvotes: 2

Related Questions