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

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

Answer

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'