Full outer join problems

Question

I'm trying to create an xml with customer specific prices for a webshop I am working on atm.

I have to join data from 6 tables in order to acomplish that.

I ran into a problem I am unable to solve for a couple of days now.

The thing is that I join customer ID with discount group (works fine),

then I join it with data that are stored in another table that connects item groups with customer discount groups (this table is not complete - it has data if someone entered discount for specific customer for specific group of items). The end result lacks all items from item groups that have no data in this table.

then I join it with item discount group table where most items have item discount group written in it.

then i tried to full outer join it with a table of items that have webshop tag enabled (so I would get all items in the result that are included in the webshop but have no discounts for a customer entered) - this thing does not work

lastly I cross apply latest prices with items. (works fine)

Any help would be gravely appreciated.

Code:

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join 
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and osnArtikel.SpletObjava='D'
and stranka.StevilkaSifranta=2
and strFaktura.StevilkaSifranta=2
and popust.StevilkaSifranta=2
and artProdaja.StevilkaSifranta=2
and strFaktura.SifraGrucePopusti<>''
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Answer 1

The problem is in your WHERE clause. This piece of code in your WHERE clause for example.

and osnArtikel.SpletObjava='D'

is going to restrict your output whenever osnArtikel.SpletObjava is NULL. Including whenever there wasn't a match on it. (your OUTER results)

One method to fix it is to move your restrictions into the ON clause. Not generally something you want to do since the ON clause is a JOIN but with OUTER JOINs it's not unreasonable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join 
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
    and strFaktura.StevilkaSifranta=2
    and strFaktura.SifraGrucePopusti<>''
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
    and popust.StevilkaSifranta=2
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
    and artProdaja.StevilkaSifranta=2
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla
    and osnArtikel.SpletObjava='D'

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
order by stranka.SifraStranke, osnArtikel.SifraArtikla

Another method is to include checks for the column being NULL in your WHERE clause. This may or may not be the way you want to go because if the column you are checking is also nullable you could end up with some unintended results. The best method is to check if a non-null column (primary key column for example) is nullable.

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join 
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and stranka.StevilkaSifranta=2
and (osnArtikel.PrimaryKeyColumn IS NULL 
        OR osnArtikel.SpletObjava='D' )
and (strFaktura.PrimaryKeyColumn IS NULL
    OR (strFaktura.StevilkaSifranta=2
        and strFaktura.SifraGrucePopusti<>'')
    )
and (popust.PrimaryKeyColumn IS NULL 
    OR popust.StevilkaSifranta=2)
and (artProdaja.PrimaryKeyColumn IS NULL 
    OR artProdaja.StevilkaSifranta=2)
order by stranka.SifraStranke, osnArtikel.SifraArtikla