CODEWITHSUNDEEP
sqloracle-databaseselect
Mato
Mato

Reputation: 225

Divide select in SQL does not work

Can you help me please with the divide select in SQL? I use the Oracle Apex Express Edition. I have three tables. In English (original names are in brackets): User (Uzivatel), Event (Udalost) and Groups of users (Skupina_u). Their context is on screens:

Now I have to use divide select in SQL. This select is formulated: You must show all users who were on all events. In my tables, you can see that I have two users, who were on all events. They are part of group “SK2.” I am using this select: 

select *
from   Uzivatel
where  not exists (select *
                   from   Skupina_u
                   where  not exists (select *
                                      from   Udalost
                                      where  Skupina_u.ID_uz = Uzivatel.ID_uz
                                      and    Skupina_u.ID_uz = Udalost.ID_uz))

But the result is: no data found.

Thank you for help.

Upvotes: 0

Views: 242

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is answering the question you pose in English . . . "Find all users who are on all events."

This is an example of a set-within-sets query. I think the best approach is using aggregation (because the same structure can be used for many questions). Let me show you using the English names for the tables and columns:

select eu.UserId
from User_Events eu
group by eu.UserId
having count(distinct eu.EventId) = (select count(*) from Events e)

That is, select all the users where the number of distinct event ids is the number in the events table. If you only want events that have users, then use the following having clause:

having count(distinct eu.EventId) = select count(distinct eventId) from Events e)

Upvotes: 3

Related Questions