Return list of rows if all records exist in another table in sql server

Question

Simple query in theory but I can't get my head around the required syntax, not sure what this type if query is called / how to word it which is making it difficult to google for a solution. SQL Server 2014

I basically want to return a list of user IDs if all of their records exist within another table.

Example

Users

ID	Name
1	User1
2	User2
3	User3
4	User4
5	User5

question from:https://stackoverflow.com/questions/65902367/return-list-of-rows-if-all-records-exist-in-another-table-in-sql-server

Answer 1

You can use join and group by. Assuming you have no duplicates in userrecords (as in your sample data):

select ur.userid
from userrecords ur join
     lookup lu
     on ur.record = lu.id
group by ur.userid
having count(*) = (select count(*) from lookup);

If you do have duplicates in userrecords, then use count(distinct record).