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.
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
You need to correlate the inner query with the outer (the inner reference to the same table should be removed):
SELECT q.id, q.class, MAX(tm.tmNum) AS nmbr FROM t1 q, (SELECT COUNT(*) as tmNum from t2 t WHERE q.id = t.event_id GROUP BY t.reconstruction_algorithm) tm WHERE q.parameterX >= 123 AND parameterY >= 20 ORDER BY q.counts DESC;
SELECT q.id, q.class, MAX(tm.tmNum) AS nmbr FROM t1 q, (SELECT t2.event_id, COUNT(*) as tmNum from t2 GROUP BY t2.event_id, t2.reconstruction_algorithm) tm WHERE tm.event_id = q.id group by q.id, q.class ORDER BY q.counts DESC