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.

  • Do you have a separate table with the list of properties? Commented Dec 20, 2014 at 16:59
  • @AaronBertrand Yes, I do...
    – JoeGeeky
    Commented Dec 20, 2014 at 17:19

1 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:

  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;
  • Tried to apply this but I am still not seeing rows that reflect the absence of a match through the junction table. The Cross Join feels right but as I said I am still missing the unmapped records.
    – JoeGeeky
    Commented Dec 20, 2014 at 23:33
  • @JoeGeeky this query gets the exact same results your question states you're expecting. Can you provide some sample data and desired results that this query doesn't deliver? Commented Dec 21, 2014 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.