Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer
    
Thank you very much, JChao. This solved the problem. –  RBo Jun 7 at 6:52

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;

Updated suggestion:

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
share|improve this answer
    
I tried this, but unfortunately I got an error saying Error Code: 1054. Unknown column 'q.id' in 'where clause'. –  RBo Jun 7 at 6:56
    
Ah, I did not look that close on your query. The subselect should not be a derived table. I'll update the suggestion –  Lennart Jun 7 at 7:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.