3 joins, get maximum value from one table

vladone · #1 (**permalink**) 07-01-10, 15:27

Hello,

I have 3 tables:

Code:

t_user                  t_request            t_cycle
- user_id             - req_id               - cycle_id
- columnA            - user_id              - req_id 
- columnB            - columnA             - time
                         - columnB             - columnA

Every t_user has one or more t_requests. Every t_request has one or more t_cycles.
I need a query that displays the t_user's t_request having the maximum t_cycle.time. Here is an example:

Code:

SELECT  t_user.user_id, max (t_cycle.time)   
FROM t_user 
INNER JOIN t_request ON t_user.user_id = t_request.user_id 
INNER JOIN t_cycle ON t_request.req_id = t_cycle.req_id
group by t_user.user_id

This query is good but I also want to display also the corresponding t_request fields and t_cycle fields. How can I do that?
IMPORTANT: the solution must not use a subquery that acts like a separate table (derived tables) like:
SELECT ... FROM ...
INNER JOIN ( SELECT ... FROM ...) AS derivedtable ON derivedtable.id = ....
The query can use correlated subqueries.

many thanks

Pat Phelan · #2 (**permalink**) 07-01-10, 15:44

The restriction makes this problem statement silly to solve. There could be many underlying restrictions that we can't infer based on the way you've stated the problem.

Please restate your problem in real world terms (ignoring how we might choose to solve the problem) so that we can help you to find a solution that meets your actual needs instead of playing guessing games for hours or days trying to find all of your constraints.

-PatP

vladone · #3 (**permalink**) 07-01-10, 16:12

Quote:

Originally Posted by Pat Phelan

Please restate your problem in real world terms ...-PatP

The restriction comes from Hibernate Query Language where you can join only entities(in my case t_user, t_request, t_cycle). Finally I have to translate the query in HQL. May be this is not the right approach but we should not discuss it here.
So let's develop my previous example and display the additional data(ignoring the restrictions ). Thank you.

r937 · #4 (**permalink**) 07-01-10, 16:37

Quote:

Originally Posted by vladone

The query can use correlated subqueries.

whew, for a minute i thought i was gonna hafta use a partial cross join

Code:

SELECT t_user.user_id  
     , t_user.columnA  
     , t_user.columnB     
     , t_request.req_id      
     , t_request.columnA     
     , t_request.columnB 
     , t_cycle.cycle_id 
     , t_cycle.time 
     , t_cycle.columnA
  FROM t_user
INNER
  JOIN t_request
    ON t_request.user_id = t_user.user_id
INNER
  JOIN t_cycle
    ON t_cycle.req_id = t_request.req_id
   AND t_cycle.time =
       ( SELECT MAX(time)
           FROM t_cycle
          WHERE req_id = t_request.req_id )

vladone · #5 (**permalink**) 07-01-10, 17:33

Quote:

Originally Posted by r937

whew, for a minute i thought i was gonna hafta use a partial cross join

Thank you for the answer. But the query is not finalized. I want to display one line for every t_user.
My data:

Code:

t_user          t_request                  t_cycle
user_id         user_id  req_id(PK)      req_id    cycle_id  time      
1                  1           1                1          1             2  
2                  1           2                1          2             6 
                    2           3                2          3             7
                                                  2          4             4
                                                  3          5             3

I want:

Code:

user_id  req_id cycle_id time 
1            2         3        7
2            3         5        3

So for every user I want the request having the cycle with the maximum time.

Your query displays:

Code:

user_id  req_id cycle_id time 
1            1         2        6
1            2         3        7
2            3         5        3

r937 · #6 (**permalink**) 07-01-10, 17:53

Code:

SELECT t_user.user_id  
     , t_user.columnA  
     , t_user.columnB     
     , t_request.req_id      
     , t_request.columnA     
     , t_request.columnB 
     , t_cycle.cycle_id 
     , t_cycle.time 
     , t_cycle.columnA
  FROM t_user
INNER
  JOIN t_request
    ON t_request.user_id = t_user.user_id
INNER
  JOIN t_cycle
    ON t_cycle.req_id = t_request.req_id
   AND t_cycle.time =
       ( SELECT MAX(c2.time)
           FROM t_request AS r2
         INNER
           JOIN t_cycle c2
             ON c2.req_id = r2.req_id
          WHERE r2.user_id = t_user.user_id )

vladone · #7 (**permalink**) 07-01-10, 18:00

Quote:

Originally Posted by r937

...

It works now. Thank you very much.

Internet Services

Web Development

Digital Marketing

Consumer Tech