Find a way to query a list of items that meet ALL of a criteria using SQL

I'm trying to do the following:

select i.FirstName, i.LastName, COUNT(1) from (
    select u.Id, uw.WidgetId from [DB].[dbo].[Widgets] w inner join
     UserWidgets uw on w.Id = uw.WidgetId inner join
     Users u on uw.UserId = u.Id
     where uw.WidgetId in ('29017318-FD89-4952-A3A2-8405BD5C5C44', 
                           'BDB7D25C-0794-4965-842D-E6开发者_开发问答D03A250418',
                          'CB4553AC-A47B-4AA6-9231-5C59C8F97655')
     group by u.Id, uw.WidgetId
     ) a
 inner join [Db2].[dbo].[Identities] i on a.Id = i.IdentityId
 group by i.LastName, i.FirstName
 order by i.LastName, i.FirstName

What I want is to ensure that the "In" statement requires that the User ONLY has those 3 Id's. No more, no less.

What is the best way to do this?

Try:

select i.FirstName, i.LastName, COUNT(1) from (
    select u.Id, uw.WidgetId from [DB].[dbo].[Widgets] w inner join
     UserWidgets uw on w.Id = uw.WidgetId 
                    and uw.WidgetId in ('29017318-FD89-4952-A3A2-8405BD5C5C44', 
                           'BDB7D25C-0794-4965-842D-E6D03A250418',
                          'CB4553AC-A47B-4AA6-9231-5C59C8F97655')
     inner join Users u on uw.UserId = u.Id
     left join UserWidgets uw2 on uw2.userid = u.id
                    and uw2.WidgetId not in ('29017318-FD89-4952-A3A2-8405BD5C5C44', 
                           'BDB7D25C-0794-4965-842D-E6D03A250418',
                          'CB4553AC-A47B-4AA6-9231-5C59C8F97655')
     where uw2.widgetid is null
     group by u.Id, uw.WidgetId
     ) a
 inner join [Db2].[dbo].[Identities] i on a.Id = i.IdentityId
 group by i.LastName, i.FirstName
 having count(1) = 3
 order by i.LastName, i.FirstName

    select i.FirstName, i.LastName, COUNT(1) from (
    select u.Id, uw.WidgetId from [DB].[dbo].[Widgets] w inner join
     UserWidgets uw on w.Id = uw.WidgetId inner join
     Users u on uw.UserId = u.Id
     where uw.WidgetId in ('29017318-FD89-4952-A3A2-8405BD5C5C44', 
                           'BDB7D25C-0794-4965-842D-E6D03A250418',
                          'CB4553AC-A47B-4AA6-9231-5C59C8F97655')
     AND count(WidgetId) = 3
     group by u.Id, uw.WidgetId
     ) a
 inner join [Db2].[dbo].[Identities] i on a.Id = i.IdentityId
 group by i.LastName, i.FirstName
 order by i.LastName, i.FirstName

I added the 'AND count(WidgetId) = 3' in to the query...i believe that would work?

This should work:

SELECT i.firstname,
       i.lastname,
       COUNT(1)
FROM   (SELECT u.id,
               uw.widgetid
        FROM   [DB].[dbo].[Widgets] w
               INNER JOIN userwidgets uw
                 ON w.id = uw.widgetid
               INNER JOIN users u
                 ON uw.userid = u.id
        WHERE  uw.widgetid IN ( '29017318-FD89-4952-A3A2-8405BD5C5C44',
                                        'BDB7D25C-0794-4965-842D-E6D03A250418'
                                        ,
                                'CB4553AC-A47B-4AA6-9231-5C59C8F97655' )
               AND NOT EXISTS (SELECT *
                               FROM   [DB].[dbo].[Widgets] w2
                                      INNER JOIN userwidgets uw2
                                        ON w2.id = uw2.id
                               WHERE  w2.id = w.id
                                      AND uw2.widgetid NOT IN (
                              '29017318-FD89-4952-A3A2-8405BD5C5C44',
                              'BDB7D25C-0794-4965-842D-E6D03A250418'
                              ,
                              'CB4553AC-A47B-4AA6-9231-5C59C8F97655' ))
        GROUP  BY u.id,
                  uw.widgetid) a
        HAVING COUNT(DISTINCT uw.widgetid) = 3
       INNER JOIN [Db2].[dbo].[Identities] i
         ON a.id = i.identityid
GROUP  BY i.lastname,
          i.firstname
ORDER  BY i.lastname,
          i.firstname