CODEWITHSUNDEEP
sqlsql-servert-sql
Dominic Rodger
Dominic Rodger

Reputation: 99751

Joining to a table with multiple rows for the join item

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.

Upvotes: 1

Views: 805

Answers (3)

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 4

A-K
A-K

Reputation: 17080

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

Upvotes: 5

ScottK
ScottK

Reputation: 126

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)

Upvotes: 0

Related Questions