Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: 3 joins, get maximum value from one table

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    Quote Originally Posted by Pat Phelan View Post
    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.
    Last edited by vladone; 07-01-10 at 17:26.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vladone View Post
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    4
    Quote Originally Posted by r937 View Post
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2010
    Posts
    4
    Quote Originally Posted by r937 View Post
    ...
    It works now. Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •