FULL OUTER JOIN not working as expected

Question

I am trying to join 3 tables to get a list of stores/codes but I want all the codes to show up even if it doesn't appear under a store (as a NULL). Here is what I have so far:

Select
    pl.Store_Number
    ,CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name as Store
    ,pc.Plan_Desc
    ,pc.Plan_Cd
    ,pl.Size
    ,pl.Position_Cd
From
    Plan pl
    INNER JOIN Store s ON s.Store_Number = pl.Store_Number
    FULL OUTER JOIN Plan_Code pc ON pc.Plan_Cd = pl.Plan_Cd
Where
    s.End_Date IS NULL
    and pl.Plan_ID <> 0
    and pc.Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
        ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)

But as a result I am only getting what matches in each table (like a normal INNER JOIN). Where am I going wrong?

EDIT:

I got a work buddie to help me with this and here is what we came up with:

Select
    pc.Store_Number
    ,CAST(pc.Store_Number as varchar) + ' - ' + pc.Store_Name as Store
    ,pc.Plan_Desc
    ,pc.Plan_Cd
    ,pl.Size
    ,pl.Position_Cd
From
    (
    Select
        Plan_Desc
        ,Plan_Cd
        ,Store_Number
        ,Store_Name
    From
        Plan_Code pc
        cross join STORE s
    Where
        Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
            ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)
        and s.End_Date is null
        and Store_Number <> 0

    ) pc 
    LEFT OUTER JOIN (
        Select  
            pl.Store_Number
            ,CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name as Store
            ,pl.Plan_Cd
            ,pl.Size
            ,pl.Position_Cd
        From
            Plan pl
            INNER JOIN Store s ON s.Store_Number = pl.Store_Number
        Where
            s.End_Date IS NULL
            and pl.Plan_ID <> 0
    ) pl ON pc.Plan_Cd = pl.Plan_Cd and pc.Store_Number = pl.Store_Number
Where
    pc.Store_Number in (Select DISTINCT Store_Number From Plan)
Order By
    Store_Number
    ,pc.Planogram_Cd

Answer 1

I would solve this with two steps, and since you want all plan numbers even without stores, you should left join your store table to the plan table.

select
    pl.store_number
    ,pc.plan_desc
    ,pc.plan_cd
    ,pl.Size
    ,pl.Position_cd
into #plans
from plan pl
    inner join plan_code pc on pc.plan_cd = pl.plan_cd
where pl.plan_id >< 0
    and pc.Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)

select
    p.Store_Number
    ,CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name as Store
    ,p.Plan_Desc
    ,p.Plan_Cd
    ,p.Size
    ,p.Position_Cd
from #plans p
left outer join store s on s.store_number = p.store_number
where s.end_date is null

Edit to my previous answer - I assume you want the two plan tables to be inner joined first then append store numbers, though its not totally clear.

Answer 2

The problem is the fact that you are referencing items from each side of the full join in the WHERE clause. For example in this line:

and pl.Plan_ID <> 0

If pl.PLan_ID is null because of the full join, you have NULL <> 0 which is not true, so any rows where pl.Plan_ID is NULL are not returned.

The same is true for pc.Plan_Cd, when this is NULL, the following line does not evaulation to true:

pc.Plan_Cd IN (1,2...

Therefore no rows will be returned where pc.Plan_Cd is NULL

I think you would need to move your where clauses inside subqueries:

SELECT  pl.Store_Number,
        pl.Store
        pc.Plan_Desc,
        pc.Plan_Cd,
        pl.Size,
        pl.Position_Cd
FROM    (   SELECT  pl.Store_Number,
                    CAST(s.Store_Number as varchar) + ' - ' + s.Store_Name AS Store,
                    pl.Size,
                    pl.Position_Cd,
                    pc.Plan_Cd
            FROM    Plan pl
                    INNER JOIN Store s 
                        ON s.Store_Number = pl.Store_Number
            WHERE   s.End_Date IS NULL
            AND     pl.Plan_ID <> 0
        ) pl
        FULL OUTER JOIN 
        (   SELECT  *
            FROM    Plan_Code pc 
            WHERE   pc.Plan_Cd IN (1,2,3,4,5,6,31,7,8,9,10,11,13,14,36,37
                        ,35,17,19,29,23,27,30,15,16,21,32,25,26,42,51,40,44,50,47,41,39)
        ) PC
            ON pc.Plan_Cd = pl.Plan_Cd;