samedi 20 décembre 2014

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


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.





Aucun commentaire:

Enregistrer un commentaire