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.
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).
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 ... ...
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)
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.