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

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

CodePudding user response：

select t.user_id
      ,t.event_type
      ,t.date_of_event

from   t join t t2 on t.user_id = t2.user_id 
         and  t.event_type <> t2.event_type
where    t.event_type = 'x'
         and          (t2.date_of_event < t.date_of_event
         or   datediff(t2.date_of_event,  t.date_of_event) > 730)

user_id	event_type	date_of_event
userA	X	2000-01-01
userB	X	2000-01-01

Fiddle

CodePudding user response：

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'