SQL way to get number of people that have seen order of name_pages (rule2) & (rule3) without seen another page using id in between - Mysql

Nnemune
March 5, 2023
242 views
0 votes
2 Answers

Table name: DocumentationBook

id	name_page
1	rule1
2	rule2
1	rule3
2	rule3
3	rule2
3	rule3

I have tried to do this,

SELECT COUNT(id)
FROM DocumentationBook
where name_page = "rule2" OR name_page = "rule3"

but it doesnt look right since i would like to show rule2 and rule3 following each other, or if there’s a method to give order of pages seen i would appreciate it so much.

Answers

- Tushar
- March 5, 2023 at 3:43 pm
- 0 votes
0
I created a SQLFIDDLE using your table data;

The following query shall return you the expected result.
```
select count(distinct id) as count_of_people
from DocumentationBook 
where id in (
  select id 
  from DocumentationBook 
  where name_page = 'rule2'
) and id in (
  select id 
  from DocumentationBook 
  where name_page = 'rule3'
);
```
Output :
```
count_of_people
2
```
without seen another page using id in between

If you want to add this condition; you can add as
```
and id not in (
  select id 
  from DocumentationBook 
  where name_page in ('rule4', 'rule5', 'rule6')
);
```
Here rule numbers you can decide as per your requirement.
Login or Signup to reply.

- SelVazi
- March 5, 2023 at 5:00 pm
- 0 votes
0
This is a way to do it using WITH clause
```
with cte as (
  select id
  from DocumentationBook
  where name_page in ('rule2', 'rule3')
  group by id 
  having count(1) = 2
),
cte2 as (
  select id
  from DocumentationBook
  where name_page not in ('rule2', 'rule3')
)
select count(1)
from cte 
where id not in (select id from cte2)
```
First cte to get ids that have seen rule2 and rule3

Second cte to get ids that have seen other rules except rule2 and rule3

Then we get only ids from fist cte that are not on cte2.

Demo here
Login or Signup to reply.