Query to merge 3 tables SQL SERVER

Question

I have a list of items in one table. These items could be of two types 'A' or 'M'

Table 1

---------------------------------------------------------
|  Code  |  Type  |  Description  |  Quantity  |  Cost  |
|-------------------------------------------------------|
|BAG001_M|   M    | Some text here|     2.0    |  40.0  |
|BAG002_M|   M    | Some text here|     5.0    |  69.0  |
|CAY003_M|   A    | Some text here|     8.0    |  10.0  |
|CFI002_M|   M    | Some text here|     8.0    |  10.0  |
--------------------------------------------------------

I need to have another column to show the Unit of Measure (UoM). This column is located in two diferrent tables. If an item is type M the UoM is located in Table A, in the other hand if it is type A the UoM is located in Table B.

Table A

-----------------------------------------------
|  Code  |  Description  |  Currency  |  UoM  |
|----------------------------------------------
|BAG001_M| Some text here|     MXN    |   m2  |
|BAG002_M| Some text here|     USD    |   lt  |
|CFI002_M| Some text here|     MXN    |   m3  |
-----------------------------------------------

Table B

-----------------------------------------------
|  Code  |  Description  |  Currency  |  UoM  |
|----------------------------------------------
|CAY003_M| Some text here|     USD    |   kg  |
-----------------------------------------------

I need to have something like this.

-----------------------------------------------------------------
|  Code  |  Type  |  Description  |  Quantity  |  Cost  |  UoM  |
|---------------------------------------------------------------|
|BAG001_M|   M    | Some text here|     2.0    |  40.0  |  m2   |
|BAG002_M|   M    | Some text here|     5.0    |  69.0  |  lt   |
|CAY003_M|   A    | Some text here|     8.0    |  10.0  |  kg   |
|CFI002_M|   M    | Some text here|     8.0    |  10.0  |  m3   |
-----------------------------------------------------------------

There is a small problem. There could be 2 items with the same Code but different Type so they may have another UoM. For example.

-----------------------------------------------------------------
|  Code  |  Type  |  Description  |  Quantity  |  Cost  |  UoM  |
|---------------------------------------------------------------|
|AAA001_M|   M    | Some text here|     2.0    |  40.0  |  m2   |
|AAA001_M|   A    | Some text here|     5.0    |  20.0  |  lt   |
-----------------------------------------------------------------

I am using SQL Server 2012 and I have tried the following code. It worked with the first inner join so I tried to add another one but It seems this is not the way of doing it.

SELECT
    CODE
    ,Type
    ,AD.DESCRIPTION
    ,QUANTITY
    ,AD.[COSTS]
  ,A.UOM
FROM 
    Table1 AS AD INNER JOIN TableA AS A
ON
    CODE = CASE
                        WHEN Type = 'M'
                            THEN A.CODE
                  END
   INNER JOIN TableB AS B
ON
    Code = CASE
                            WHEN Type = 'A'
                                THEN B.CODE
                      END

Can someone help me out here?

Answer 1

Here's a way to do this with two Left Joins:

Select      AD.CODE,
            AD.Type,
            AD.DESCRIPTION,
            AD.QUANTITY,
            AD.[COSTS],
            Coalesce(A.UOM, B.UOM) As UOM
From        Table1  AD
Left Join   TableA  A   On  A.Code = AD.Code
                        And AD.Code = 'M'
Left Join   TableB  B   On  B.Code = AD.Code
                        And AD.Code = 'A'

The Coalesce() will select the first non-NULL value from the two results.

---

In the case where an item has multiple codes and you need both results, you can use a UNION ALL instead:

Select  AD.CODE,
        AD.Type,
        AD.DESCRIPTION,
        AD.QUANTITY,
        AD.[COSTS],
        A.UOM
From    Table1  AD
Join    TableA  A   On  A.Code = AD.Code
Where   AD.Code = 'M'
Union All
Select  AD.CODE,
        AD.Type,
        AD.DESCRIPTION,
        AD.QUANTITY,
        AD.[COSTS],
        B.UOM
From    Table1  AD
Join    TableB  B   On  B.Code = AD.Code
Where   AD.Code = 'A'