Is it possible to use result or state of query in same query

Issue

For example I have this table events:

user_id event_type date_of_event
userA X 01-01-2000
userA Y 01-01-2005
userB X 01-01-2000
userB Y 01-01-2100

I want to select all users that have an event_type = X but no event_type Y in the next 2 years AFTER the date of event_type X. So in the table above that would be userA and userB

I know I can do this in 2 mysql queries but was just wondering if there is a way to do this in just 1 query

Solution

You can do it with a self-join.

SELECT DISTINCT a.user_id
FROM yourTable AS a
LEFT JOIN yourTable AS B 
    ON a.user_id = b.user_id AND b.date_of_event < DATE_ADD(a.date_of_event, INTERVAL 2 YEAR)
    AND b.event_type = 'Y'
WHERE a.event_type = 'X'

Answered By – Barmar

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published