MySQL - Join these two queries together with a join or subquery?

Question

This query returns the data I require given a specific traceid value (50 in this example, which is a key that exists in both tables, tblResults and tblTraces:

SELECT count(changed)
FROM (
    SELECT changed
    FROM tblResults
    WHERE traceid = 50
    AND changed = 1
    ORDER BY resultid DESC
    LIMIT 0,20
) as R

I want to run this above query against nearly every traceid (so, selecting 20 rows for each traceid, not just traceid 50, so 3 traceid's would mean 60 rows). This simple select below gets the required traceid values:

SELECT `traceid` FROM `tblTraces` WHERE `enabled` = 1

How can I "glue" the two queries together?

So I imagine a query like the following, except it won't work because the subquery is returning multiple rows (which I want):

SELECT count(changed)
FROM (
    SELECT changed
    FROM tblResults
    WHERE traceid = (
                     SELECT `traceid` FROM `tblTraces` WHERE `enabled` = 1
                    )
    AND changed = 1
    ORDER BY resultid DESC
    LIMIT 0,20
) as R

Here is an sqlfiddle to show the schema of these two tables: http://sqlfiddle.com/#!2/c04125/1/0 — jwbensley, Mar 23 '13 at 20:50
Do you really mean both `count(changed)` and `changed = 1` ? This will return the number of "changed" rows with a maximum of 20. — Gordon Linoff, Mar 23 '13 at 20:58
Hmm, so there are many rows in `tblResults` with the same `traceid`. I want `count(changed)` of the 20 most recent rows/entries for each `traceid` in `tblResults` (so `ORDER BY resultid DESC LIMIT 0,20`) where `changed = 1` — jwbensley, Mar 23 '13 at 21:02
The most recent revision of you answer seems to be working, I just need to double check — jwbensley, Mar 23 '13 at 21:05

score 1 · Answer 1 · edited May 23 '17 at 10:25

Here's another way to do this. I adapted Bill Karwin's example. See his post for a more thorough explanation.

SELECT x.traceId, COUNT(*)
FROM (
  SELECT a.*
    FROM tblResults a
    -- Be sure not to exclude tblResults which are the only record in the trace
    LEFT OUTER JOIN tblResults b
      -- We're going to count how many rows came "before" this one
      ON a.changed = b.changed
     AND a.traceId = b.traceId
     AND a.resultId < b.resultId
   WHERE a.changed = 1
     AND a.traceId IN (SELECT traceId FROM tblTraces WHERE enabled = 1)
   GROUP BY a.resultId
  HAVING COUNT(*) < 20      -- Eliminate rows ranked higher than 20
   ORDER BY b.resultId DESC -- when we rank them by resultid
) x GROUP BY x.traceId

The outer query is only for verification that each traceid contains no more than 20 selected records, and the inner query is the result set you're actually interested in.

You need `LEAST()`, not `MIN()` there. But I had the same thought. — ypercubeᵀᴹ, Mar 23 '13 at 21:01

score 1 · Answer 2 · answered Mar 23 '13 at 21:11

Although I'm not satisfied with this answer, it's probably the easiest for smaller tables:

SELECT tblTraces.traceid, LEAST(20, SUM(tblResults.changed))
FROM tblTraces
    LEFT JOIN tblResults
    ON tblTraces.traceid = tblResults.traceid
WHERE tblTraces.enabled = 1
AND tblResults.traceid.changed = 1
GROUP BY tblTraces.traceid

The limit on column really complicates the joining.

score 1 · Answer 3 · answered Mar 23 '13 at 21:14

It's not exactly clear if you need a count of these (limited to 20 per trace) results or the actual results. For the count only, there is a simple way:

  SELECT t.traceid,
         LEAST(COUNT(*),20) AS cnt
  FROM tblTraces AS t
    JOIN tblResults AS r
      ON r.traceid = t.traceid
  WHERE t.enabled = 1
    AND r.changed = 1
  GROUP BY t.traceid ;

test in SQL-Fiddle

score 0 · Answer 4 · answered Mar 23 '13 at 20:33

0

Can you just do an inner join like so

select count(traceid)
  from tblResults a inner join tblTraces b on a.traceid = b.traceid
   and b.enabled = 1
   and a.changed = 1

Or am I missing something else here?

answered Mar 23 '13 at 20:33

Jerry Hoerig

146
9

1

The `LIMIT 20` per trace complicates things. – ypercubeᵀᴹ Mar 23 '13 at 20:45

Gordon Linoff · Accepted Answer · 2013-03-23T21:01:33.647

In MySQL, there is no easy way to do this. In other databases, you would just use row_number(), but it is not available.

Here is one approach, assuming resultId is unique on each row:

select t.traceId, count(changed)
from (select traceid, ResultId, changed,
             (select count(*) from tblResults r2 where r2.traceId = r.traceId and r2.ResultId >= r.ResultId and r2.changed = 1) as seqnum
      from tblResults r join
           tblTraces t
           on r.TraceId = t.TraceId and t.enabled = 1
      where r.changed = 1
    ) t
where seqnum <= 20
group by traceId

If you don't want this by traceid and just want the total count, then remove the group by and change the select to count(changed).

Phew! That was quite the noodle baker! So, this works perfectly. I just had to remove `and r2.changed = 1`, I have manually checked by hand the answers it works perfectly. Now I need to make a coffee and spend some time fully understanding this answer, its great. Thanks! — jwbensley, Mar 23 '13 at 21:21

MySQL - Join these two queries together with a join or subquery?

5 Answers5