[SQL] How do I 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.

Note: I am using SQL Server.

Continue reading...