views:

181

answers:

3

I have a table users which has a primary key userid and a datetime column pay_date.

I've also got a table user_actions which references users via the column userid, and a datetime column action_date.

I want to join the two tables together, fetching only the earliest action from the user_actions table which has an action_date later than or equal to pay_date.

I'm trying things like:

select users.userid from users
left join user_actions on user_actions.userid = users.userid
where user_actions.action_date >= users.pay_date
order by user_actions.pay_date

But obviously that returns me multiple rows per user (one for every user action occurring on or after pay_date). Any idea where to go from here?

Apologies for what probably seems like a simple question, I'm fairly new to t-sql.

+4  A: 

If you have a PRIMARY KEY on user_actions:

SELECT  u.*, ua.*
FROM    users u
LEFT JOIN
        user_actions ua
ON      user_actions.id = 
        (
        SELECT  TOP 1 id
        FROM    user_actions uai
        WHERE   uai.userid = u.userid
                AND uai.action_date >= u.pay_date
        ORDER BY
                uai.action_date
        )

If you don't:

WITH    j AS
        (
        SELECT  u.*, ua.*, ROW_NUMBER() OVER (PARTITION BY ua.userid ORDER BY ua.action_date) AS rn, ua.action_date
        FROM    users u
        LEFT JOIN
                user_actions ua
        ON      ua.userid = u.userid
                AND ua.action_date >= u.pay_date
        )
SELECT  *
FROM    j
WHERE   rn = 1 or action_date is null

Update:

CROSS APPLY proposed by @AlexKuznetsov is more elegant and efficient.

Quassnoi
Does that assume the user_Actions table has an id?
Dominic Rodger
The first solutions does (as the post says), the second one does not.
Quassnoi
Thanks - that's almost exactly what I want. Is it possible to keep users with whom no such action exists? I only want one value (`ua.action_date`) out of the `user_actions` table, and I'd like that to be null if no such action exists. No worries if you've not got time, you answered my question perfectly, so I've +1ed you, and will mark your answer accepted regardless.
Dominic Rodger
@Dominic: sure, just replace JOIN with LEFT JOIN
Quassnoi
@Quassnoi - replacing JOIN with LEFT JOIN will only pull you back the first row in `users` for which there are no `user_actions` (since `rn=1`), adding `or ua.action_date is null` did the trick.
Dominic Rodger
A: 
select u.*, ua.* from
    users u join users_actions ua on u.userid = ua.userid
where
    ua.action_date in
            (select min(action_date) from user_actions ua1
             where
                  ua1.action_date >= u.pay_date and
                  u.userid=ua1.userid)
ScottK
+5  A: 

CROSS APPLY is your friend:

select users.*, t.* from users
CROSS APPLY(SELECT TOP 1 * FROM user_actions WHERE user_actions.userid = users.userid
AND user_actions.action_date >= users.pay_date
order by user_actions.pay_date) AS t
AlexKuznetsov
Nice point, +1 .
Quassnoi
This is a fair bit slower than @Quassnoi's suggestion, and doesn't account for `users` with no `user_actions`. Answers the original question though, so +1!
Dominic Rodger