[Users]
ID | UserID | City | Phone
----+-----------+-----------+----------
1 | John | Rome | 12345
2 | Tom | Oslo | 12345
3 | Simon | Bogota | 12345
4 | Kurt | Tokyo | 12345
[Orders]
ID | UserID | OrderNr | OrderName
------------------------------------------------
1 | John | 1 | Apple
2 | John | 2 | Carrots
3 | John | 3 | Banana
4 | Tom | 3 | Banana
5 | Tom | 1 | Apple
6 | Tom | 8 | Raisins
7 | Simon | 3 | Banana
8 | Simon | 1 | Apple
9 | Kurt | 7 | Cucumber
Approved List
1 (Apple)
3 (Banana)
4 (Another order)
8 (Raisins)
Now i would like to select all Users who's orders only contains/matches my approved list.
John should be excluded in this case because he ordered Carrots which has OrderNr 2 that is not in my approved list.
If you want users who only have orders in the approved list, I would suggest conditional aggregation and a having clause:
select o.userid
from orders as o
group by o.userid
having sum(iif(o.OrderNr not in (1, 3, 4, 8), 1, 0)) = 0;
The having clause filters out users who have an "unapproved" product.