How to achieve the desired output in SQL Server 2008?

tbl_Fuel #Temp1

CommodityID Commodity      Fuel_Sector  Fuel_Rate   Fuel_Min    Fuel_EffectiveDate
----------------------------------------------------------------------------------------------------
1         General Cargo      OTHERS     38.00       0.00        01/Apr/2019
1         General Cargo    GULF+M.EAST  27.00       0.00        01/Apr/2019
1         General Cargo     C/D        16.00        0.00        01/Apr/2019

2   Perishable - General    OTHERS      7.00        0.00        15/Jun/2015
2   Perishable - General    GULF+M.EAST 7.00        0.00        15/Jun/2015
2   Perishable - General    C/D         7.00        0.00        15/Jun/2015

tbl_War #Temp2

CommodityID Commodity   War_Sector  War_Rate    War_Min     War_EffectiveDate
--------------------------------------------------------------------------------------------
1       General Cargo   OTHERS      7.00        0.00        01/Apr/2019
1       General Cargo   GULF+M.EAST 5.00        0.00        01/Apr/2019
1       General Cargo   CD         5.00        0.00        01/Apr/2019
1       General Cargo   BGW         5.00        0.00        01/Apr/2019

3       Cut Flowers     OTHERS      2.00        0.00        15/Jun/2015
3       Cut Flowers     GULF+M.EAST 2.00        0.00        15/Jun/2015

tbl_XRay #Temp3

CommodityID Commodity   XRay_Sector XRay_Rate   XRay_Min    XRay_EffectiveDate
--------------------------------------------------------------------------------------------
1   General Cargo       ALL     2.00        225.00      16/Nov/2019
9   Pharma & Chemicals  ALL     2.00        225.00      01/Jun/2011

Desired output:

CommodityID Commodity     | Fuel_Sector  |  Fuel_Rate  |    Fuel_Min   |    Fuel_EffectiveDate |War_Sector  |  War_Rate  |  War_Min  |  War_EffectiveDate  | XRay_Sector  | XRay_Rate  | XRay_Min   |   XRay_EffectiveDate

1         General Cargo   | OTHERS       |  38.00      |    0.00       |    01/Apr/2019        | OTHERS |  7.00      |  0.00     |  01/Apr/2019    |    ALL   |     2.00   | 225.00     |   16/Nov/2019
1         General Cargo   | GULF+M.EAST  |  27.00      |    0.00       |    01/Apr/2019        | GULF+M.EAST|  5.00      |  0.00     |  01/Apr/2019        |              |            |            |
1         General Cargo   | C/D      |  16.00      |    0.00       |    01/Apr/2019        | CD    |  5.00      |  0.00     |  01/Apr/2019        |              |            |            |
1             General Cargo   | NULL         |   NULL      |     NULL      |      NULL             |  BGW   |  5.00      |  0.00     |  01/Apr/2019        |              |            |            |

2   Perishable - General  | OTHERS       |  7.00       |    0.00       |    15/Jun/2015        |            |            |           |                         |              |            |            |
2   Perishable - General  | GULF+M.EAST  |  7.00       |    0.00       |    15/Jun/2015        |            |            |           |                         |              |            |            |
2   Perishable - General  | C/D      |  7.00       |    0.00       |    15/Jun/2015        |            |            |           |                         |              |            |            |

3       Cut Flowers   |          |             |           |               |OTHERS  |  2.00      |  0.00     |  15/Jun/2015        |              |            |            |
3       Cut Flowers   |          |         |           |               |GULF+M.EAST |  2.00      |  0.00     |  15/Jun/2015        |              |            |            |

9   Pharma & Chemicals    |              |             |           |                       |            |        |       |             |    ALL   | 2.00   |  225.00    |    01/Jun/2011

After so many Condn.. I get the data in three different temp tables… War, Fuel, XRay… I want to show the data commodity wise, if available….

I tried this query:

SELECT * 
INTO #Temp4 
FROM
    (SELECT DISTINCT AirLine, CommodityId FROM #Temp1
     UNION ALL
     SELECT DISTINCT AirLine, CommodityId FROM #Temp2
     UNION ALL 
     SELECT DISTINCT AirLine, CommodityId FROM #Temp3) a

SELECT * 
FROM #Temp4

SELECT * 
FROM #Temp4 a1
LEFT OUTER JOIN #Temp1 a2 ON a1.AirLine = a2.AirLine
                          AND a1.CommodityId = a2.CommodityId
LEFT OUTER JOIN #Temp2 a3 ON a1.AirLine = a3.AirLine
                          AND a1.CommodityId = a3.CommodityId
LEFT OUTER JOIN #Temp3 a4 ON a1.AirLine = a4.AirLine
                          AND a1.CommodityId = a4.CommodityId

This query first DISTINCT data hold in another temp table, then I’m doing a few Left Joins with all temp tables, but it’s not returning the correct output.

Answer

You can use full join, but to avoid a Cartesian product for each commodityID, you need a sequence number:

select coalesce(f.commodityID, w.commodityID, x.commodityID) as commodityID,
       coalesce(f.commodity, w.commodity, x.commodity) as commodity,
       f.fuel_sector, f.fule_rate, . . . 
        -- other columns from "tbl_fuel"
        w.war_sector, . . . 
        x.xray_sector, . . . 
from (select f.*,
             row_number() over (partition by commodityID order by commodityID) as seqnum
      from tbl_fuel f
     ) f full join
     (select w.*,
             row_number() over (partition by commodityID order by commodityID) as seqnum
      from tbl_war w
     ) w
     on w.commodityID = f.commodityID and
        w.seqnum = f.seqnum  full join
     (select x.*,
             row_number() over (partition by commodityID order by commodityID) as seqnum
      from tbl_xray x
     ) x
     on x.commodityID = coalesce(f.commodityID, w.commodityID) and
        x.seqnum = coalesce(f.seqnum, w.seqnum);