How to join fixed row table against mapping table and reveal unmapped records

Question

I have two tables and I am having trouble figuring out how to join them to meet my projection needs. Here is a scenario that illustrates the problem

1. The first table has a fixed set of values/rows; in this case Room Types

Room Types

Id | RoomType
--------------
1  | Bed Room
2  | Kitchen
3  | Half-Bath

2. The second table maps the items from the first table to an instance of a Property. One Property can have 1 or more Room Types

Property Rooms

Property | RoomTypeId
---------------------
  ABC    |     1
  ABC    |     3
  EFG    |     1
  XYZ    |     1
  XYZ    |     2
  XYZ    |     3

Note: some of the above properties are not mapped to some Room Types

3. I would like to make a projection that joins the rows in such a way that I can easily account for Room Types that are mapped and not mapped to a Property. Here is a sample result:

Property | RoomTypeId | PropertyHasRoom
---------------------------------------
   ABC   |     1      |      Yes
   ABC   |     2      |   No or NULL
   ABC   |     3      |      Yes
   EFG   |     1      |      Yes
   EFG   |     2      |   No or NULL
   EFG   |     3      |   No or NULL
   XYZ   |     1      |      Yes
   XYZ   |     2      |      Yes
   XYZ   |     3      |      Yes

Can anyone explain how this can be accomplished in T-SQL.

Answer 1

You need to create a multiplication first - every combination of Property + RoomType. In SQL Server we use a CROSS JOIN for that. Then you need to outer join to the junction table, and conditionally display Yes/No based on whether the junction table had a matching row. This should provide the output you want:

SELECT 
  p.Property, 
  RoomTypeID = t.Id, -- why isn't this just called RoomTypeID? 
  PropertyHasRoom = CASE
    WHEN pr.Property IS NOT NULL THEN 'Yes' 
    ELSE 'No' END -- or just END if you want NULL
FROM dbo.Properties AS p
CROSS JOIN dbo.RoomTypes AS t
LEFT OUTER JOIN dbo.PropertyRooms AS pr
ON p.Property = pr.Property
AND t.Id = pr.RoomTypeID
ORDER BY p.Property, t.Id;