How to perform a JOIN Query in my criteria in SQL Server?

Question

I am trying avoid Duplicate. Some times it shows duplicate records, I don't understand What I did wrong.

Here is my query

select 
        itemid = case when JEDnineDays.ItemID is null and JEDthirtyDays.ItemID is null and MECnineDays.ItemID is null then MECthirtyDays.ItemID
              when JEDnineDays.ItemID is null and JEDthirtyDays.ItemID is null then MECnineDays.ItemID
              when JEDnineDays.ItemID is null then JEDthirtyDays.ItemID
              else JEDnineDays.ItemID END
            ,convert(DECIMAL(10,2),JEDnineDays.NineDaysSold) JEDNineDaysSold
        ,convert(DECIMAL(10,2),MECnineDays.NineDaysSold) MECNineDaysSold
            ,convert(DECIMAL(10,2),JEDthirtyDays.ThirtyDaysSold) JEDThirtyDaysSold
        ,convert(DECIMAL(10,2),MECthirtyDays.ThirtyDaysSold) MECThirtyDaysSold
    into #days
    from 
    (select 
        itemid,
        sum(qtysold) as NineDaysSold
        from 
        [JC_ItemDSP10days]
    Where StoreID IN ('1201','1302','1400','1500')
        group by 
        ItemID
        ) as JEDnineDays

    full outer join
    (select 
        itemid,
        sum(qtysold) as NineDaysSold
        from 
        [JC_ItemDSP10days]
    Where StoreID IN ('2001','2400','2200')
        group by 
        ItemID
        ) as MECnineDays
    on(JEDnineDays.itemid = MECnineDays.itemid)
    full outer join 
    (select 
        itemid,sum(qtysold) as ThirtyDaysSold
        from 
        [JC_ItemDSP30Days]
    Where StoreID IN ('1201','1302','1400','1500')
        group by 
        ItemID
        ) as JEDthirtyDays
        on(JEDnineDays.itemid = JEDthirtyDays.itemid)
    full outer join 
    (select 
        itemid,sum(qtysold) as ThirtyDaysSold
        from 
        [JC_ItemDSP30Days]
    Where StoreID IN ('2001','2400','2200')
        group by 
        ItemID
        ) as MECthirtyDays
        on(JEDnineDays.itemid = MECthirtyDays.itemid)

Here is my result with Duplicate

ItemID    JEDNineDaysSold    MECNineDaysSold    JEDThirtyDaysSold    JEDThirtyDaysSold
391         NULL               NULL               0.75                   NULL
391         NULL               NULL               NULL                   2.50

Most of the records are correct. But some of them are duplicate. What is wrong with my join

Answer 1

I formatted the SQL so it was more readable. Also, the case statement at the top should just be a coalesce() or isnull() instead of a case statement.

The issue, I think, is that JEDnineDays doesn't actually contain all the ItemIds that are getting returned back so your other joins aren't joining with each other. You need to add multiple more conditions to your join statements.. something like:

on (JEDnineDays.itemid = MECnineDays.itemid OR JEDthirtyDays.itemid = MECnineDays.itemid OR ... etc)

Alternatively, and probably better, would be to do the FROM Item table and then join the rest of the tables. This will ensure that all tables can be joined back to an existing item. Once you KNOW that you will have all ItemIds in the FROM clause, you can change all your full outer joins to left joins if you want.

Fixed SQL:

select 
    itemid
    ,convert(DECIMAL(10,2),JEDnineDays.NineDaysSold) JEDNineDaysSold
    ,convert(DECIMAL(10,2),MECnineDays.NineDaysSold) MECNineDaysSold
    ,convert(DECIMAL(10,2),JEDthirtyDays.ThirtyDaysSold) JEDThirtyDaysSold
    ,convert(DECIMAL(10,2),MECthirtyDays.ThirtyDaysSold) MECThirtyDaysSold
into #days
from Items i
full outer join (
    select itemid, sum(qtysold) as NineDaysSold
    from [JC_ItemDSP10days]
    where StoreID IN ('1201','1302','1400','1500')
    group by ItemID
) as JEDnineDays on(JEDnineDays.itemid = i.itemid)
full outer join (
    select itemid, sum(qtysold) as NineDaysSold
    from [JC_ItemDSP10days]
    where StoreID IN ('2001','2400','2200')
    group by ItemID
) as MECnineDays on(i.itemid = MECnineDays.itemid)
full outer join (
    select itemid, sum(qtysold) as ThirtyDaysSold
    from [JC_ItemDSP30Days]
    where StoreID IN ('1201','1302','1400','1500')
    group by ItemID
) as JEDthirtyDays on(i.itemid = JEDthirtyDays.itemid)
full outer join (
    select 
    itemid, sum(qtysold) as ThirtyDaysSold
    from [JC_ItemDSP30Days]
    where StoreID IN ('2001','2400','2200')
    group by ItemID
) as MECthirtyDays on(i.itemid = MECthirtyDays.itemid)