How to pull data from the database with certain characteristics, avoiding the occurrence of others?

Question

SQL

There is a table with client metrics, there is a session identifier (session_id), there is the name of the action (action) that the user made.

It is necessary to unload the sessions of those users who, within the framework of one session, have a clear list of actions that they have done.

For example, unload those sessions where the session consists of actions (the order of action does not matter): 'entry', 'go to page 1', 'go to page 2', 'target action'

In total, 15 different actions are recorded in the table, you need avoiding the rest, pull out only the necessary sessions.

I tried to do it like this:

select session_id, action
from table
where action in ('вход', 'переход на страницу 1', 'переход на страницу 2', 'целевое действие')

But when checking the query

select *
from table
where session_id = 'уникальный ид сессии'

I see that there are other actions in the session that do not suit me

Tell me how to correctly form a sql query

Answer 1

Traverse the session table to itself by the session id, and add a not in exclusion clause on the connected table.

select distinct t1.session_id, t1.action
from table t1
join table t2 on t1.session_id = t2.session_id
where t1.action in ('вход', 'переход на страницу 1', 'переход на страницу 2', 'целевое действие')
and t2.action not in (перечень того, что не должно быть точно)

Option 2.

select t1.session_id, t1.action
from table t1
where t1.action in ('вход', 'переход на страницу 1', 'переход на страницу 2', 'целевое действие')
and not exists(select 1 from table t2 where t1.session_id = t2.session_id and t2.action in (перечень того, что не должно быть точно))