Get the distinct values from column X that are common to all values in column Y?

Let's say I have a table called Building_Rooms that has the following columns:

• ID - Just a normal ID
• Address - An nvarchar street address
• RoomType - An nvarchar room type (e.g., bedroom, kitchen, bathroom, etc.)

(I know this looks like bad database design, but don't worry about that. I am trying to come up with the most bare-bones, easy-to-grasp illustrative example.)

How can I get the distinct RoomType values that exist for every Address value in the table?

For example, if the table contains both office buildings and residential buildings, the results would not have bedroom because most the office buildings don't have bedrooms, they would not have boardroom because most of the residences don't have boardrooms, and so on. We would probably expect the results to be nothing more than bathroom because that would be the only RoomType value that exists for every Address value on the table.

Reproducible example code:

create table [Building_RoomTypes]
(
    [ID] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [Address] varchar(255),
    [RoomType] varchar(255)
)

insert into [Building_RoomTypes]
values (NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'boardroom'),
(NEWID(), '100 Wall Street', 'kitchen'),
(NEWID(), '100 Wall Street', 'lobby'),
(NEWID(), '100 Wall Street', 'bathroom'),
(NEWID(), '100 Wall Street', 'bathroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'kitchen'),
(NEWID(), '54 Residential Road', 'bathroom'),
(NEWID(), '54 Residential Road', 'living room'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'bedroom'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'kitchen'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'bathroom')

Expected results if successfully querying for all distinct RoomType values that exist for all distinct Address values:

RoomType
--------
bathroom
kitchen

SQL Server version number: 14.0.2065.1

Unless I'm missing something obvious, you just need a HAVING clause that uses COUNT(DISTINCT)

create table [Building_RoomTypes]
(
    [ID] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [Address] varchar(255),
    [RoomType] varchar(255)
)

insert into [Building_RoomTypes]
values (NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'office'),
(NEWID(), '100 Wall Street', 'boardroom'),
(NEWID(), '100 Wall Street', 'kitchen'),
(NEWID(), '100 Wall Street', 'lobby'),
(NEWID(), '100 Wall Street', 'bathroom'),
(NEWID(), '100 Wall Street', 'bathroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'bedroom'),
(NEWID(), '54 Residential Road', 'kitchen'),
(NEWID(), '54 Residential Road', 'bathroom'),
(NEWID(), '54 Residential Road', 'living room'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'bedroom'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'kitchen'),
(NEWID(), '890 Downtown Avenue - Unit 1', 'bathroom')
 

18 rows affected

SELECT
  RoomType
FROM
  Building_RoomTypes
GROUP BY
  RoomType
HAVING
  COUNT(DISTINCT Address)
  =
  (SELECT COUNT(DISTINCT ADDRESS) FROM Building_RoomTypes)

fiddle