'How to index match with conditions in sql

I have tables like this:

regist table

userID registDate
1 2022-01-22
2 2022-01-23

session table

userID date_key traffic
null 2022-01-02 facebook
1 2021-01-03 facebook
1 2021-01-04 google
1 2021-01-05 linkedin
2 2021-01-15 facebook
2 2021-01-25 facebook
3 2021-01-20 facebook

Output

userID date_key traffic regist date
1 2021-01-03 facebook 2022-01-22
1 2021-01-04 google 2022-01-22
1 2021-01-05 linkedin 2022-01-22
2 2021-01-15 facebook 2022-01-23

How do I merge the tables so that I can return the regist date. Do I do a right join?

Is this correct?

select * 
from sessiontables st 
left join registtable rt on st.userID = rt.userID
where st.userID is not null

How to do exist userID exist in regist table statement?

sql


Solution 1:[1]

if I understand correctly, You can try to use self join with an aggregate function.

select rt.userID,
       st.date_key,
       st.traffic,
       rt.registDate
from (
    SELECT userID,min(date_key) date_key,traffic
    FROM sessiontables
    GROUP BY traffic,userID
) st
JOIN registtable rt 
ON st.userID=rt.userID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih

Related Questions