Query to merge 3 tables SQL SERVER

Viewed 879

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?

1 Answers
Related