MySQL nested queries

MySQL

I'm working on a project using a MySQL database and we'd like to combine a query which was previously made in several steps into one step.
The queries are created in a Java-App using Hibernate.

Goal
The goal of the query (or queries up to now) is the following: Loading a subset of data from several different tables (according to some filters and sorted).

Database Structure
The structure of the database can not be changed. I'll show here the needed tables with some example entries.

Table t1

id         class    counts      ... other parameters
-------    -----    ------
1          B5.2     124831
2          A7.9     83482
3          M5.5     53124812


Table t2

event_id    reconstruction_algorithm       ... other parameters
--------    ------------------------
1           0
1           0
1           0
1           1
1           1
1           1
1           2
1           2
1           2
1           3
2           0
2           0
2           0
2           0
2           1
2           1
2           1
2           2
2           2
2           2
2           3
2           3
...         ...

Desired Result
I'm trying to find a query which can (in one step) do the following:

  • Get selected entries of table t1 (e.g. id and class) for some rows which meet some filter criteria (e.g. counts between 100000 and 150000)
  • Get the maximum amount of entries withe the same reconstruction_algorithm which belong to a single entry in t1 (e.g for the entry with id 1 this would be 3)

The problem

Up to now we used two requests. The first was used to get the entries of t1 which meet the filter criteria:

SELECT class, counts FROM t1 WHERE parameterX <= 123 AND parameterY >= 20;

The second was used to get the according amount of entries from t2:

SELECT MAX(tm.tNum) FROM (SELECT COUNT(*) as tNum from t2 t, t1 q WHERE q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm;

Now I'd like to combine those two queries into one query. I tried doing this the following way:

SELECT q.id, q.class, MAX(tm.tmNum) AS nmbr
FROM t1 q, (SELECT COUNT(*) as tmNum from t2 t, t1 q WHERE
                q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm
WHERE q.parameterX >= 123 ORDER BY q.counts DESC;

The problem is now, that this query (tried in different ways) does return only a single row, although the outer query returns several rows. So I'd like the inner query to be run for every result of the outer query.

Is there a way to achieve this?

I hope somebody knows the answer to this problem.

Best Answer

I think this query does what you are asking. The sub select gets the counts for each id. I then joined it back to t1. The outer query gets the max for each id. I didn't put the WHERE clause since I don't know the criteria but you can easily add it.

Here's a link to SQL Fiddle

select id, 
  class, 
  counts, 
  max(numOcc) as Occ
from t1
left join 
(SELECT event_id, reconstruction_algorithm, count(1) as numOcc from t2 
  group by event_id, reconstruction_algorithm) a
on t1.id = a.event_id
group by t1.id, t1.class, t1.counts
Related Question