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 | |
| 1 | 2021-01-03 | |
| 1 | 2021-01-04 | |
| 1 | 2021-01-05 | |
| 2 | 2021-01-15 | |
| 2 | 2021-01-25 | |
| 3 | 2021-01-20 |
Output
| userID | date_key | traffic | regist date |
|---|---|---|---|
| 1 | 2021-01-03 | 2022-01-22 | |
| 1 | 2021-01-04 | 2022-01-22 | |
| 1 | 2021-01-05 | 2022-01-22 | |
| 2 | 2021-01-15 | 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?
CodePudding user response：
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