CODEWITHSUNDEEP
sqlpostgresql
auxbuss
auxbuss

Reputation: 519

Joining a subquery with multiple associcated rows in subquery

I have a query that selects a set of Users. Each User can have a number of Events associated with it. I want to join each User with the earliest Event associated with that User (resulting in one row per User), and do so within a single query.

So, I kind of want to do this:

SELECT * FROM users
  left join (
    select * from events where events.user_id = users.id
    order by start_time limit 1) as event
    ON ("event"."user_id" = "users"."id")

but it is illegal to reference 'users' within the join's select.

Upvotes: 0

Views: 58

Answers (2)

Taryn
Taryn

Reputation: 247880

You can use a subquery to get the min(start_time) for each user_id. Then you will use this result to join back to the events table to get the details of the min event:

SELECT * 
FROM users u 
LEFT JOIN 
(
  SELECT Min(start_time) Min_Start, user_id 
  FROM events 
  GROUP BY user_id
) e1 
   ON u.id = e1.user_id 
LEFT JOIN events e2 
 ON e1.user_id = e2.user_id 
 AND e1.min_start = e2.start_time

If you are using a database that has the ability to apply a row_number(), then you could use the following:

select *
from
(
  SELECT *, 
    row_number() over(partition by e.user_id order by start_time) rn
  FROM users u 
  LEFT JOIN events e 
   ON u.id = e.user_id 
) src
where rn = 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In most databases, you can use row_number() for this:

SELECT *
FROM users u left join
     (select e.*, row_number() over (partition by e.user_id order by start_time) seqnum
      from events e
     ) e
     on e.user_id = u.id

MySQL and MS Access do not support this function, but most other databases do (and you do not specify what database you are using).

Upvotes: 1

Related Questions