CODEWITHSUNDEEP
mysql
Paul
Paul

Reputation: 419

Determine entrys with "missing" subsequent entry in SQL

I have a table in SQL which represent some sort of events. I try to determine all lines where a specific event is not "followed" by another specific event. In the table below this would be all lines where somebody signed up but did not sign out afterwards (Lucy and Joe). Is this achievable with SQL and if yes how?

|Id|Name      |Event     |
==========================
|01|Fred      |Sign up   |
-------------------------
|02|Joe       |Sign up   |
--------------------------
|03|Lucy      |Sign up   |
--------------------------
|04|Joe       |Do foo    |
--------------------------
|05|Joe       |Sign out  |
--------------------------
|06|Joe       |Sign up   |
--------------------------
|07|Fred      |Sign out  |
--------------------------

Many Thanks

Upvotes: 1

Views: 76

Answers (3)

Tamim Al Manaseer
Tamim Al Manaseer

Reputation: 3724

Closest thing I came up with was to see who has a full "Sign In" "Sign out" pair.

SELECT Id, Name
FROM 
    EVENTS
WHERE
     event = 'Sign up' or event = 'Sign out'
group by
    Name
having Mod(count(*), 2) <> 0

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

SELECT x.* 
  FROM events x 
  LEFT 
  JOIN events y 
    ON y.name = x.name 
   AND y.id > x.id 
   AND y.event ='sign out' 
 WHERE x.event = 'sign up' 
   AND y.id IS NULL;

or something like that

Upvotes: 3

LoztInSpace
LoztInSpace

Reputation: 5697

select * from 
table as t1 left outer join table as t2
on(t1.name=t2.name and t2.event='sign out')
where
t1.event='sign up' and t2.name is null

There's no such thing as "followed by" unless you define it so you'll need to provide more info. (It might be adding "t1.id >t2.id" in the ON clause)

Upvotes: 1

Related Questions