join

I'm trying to get rid of the side effect of the

left join catalog_promosimple

That means I'm expecting at least one row no matter if there is an
entry in
left join catalog_promosimpleitem
but I don't want "duplicates" from
catalog_promosimple

create table catalog_promosimple (
PromoSimpleID int primary key,
IsPromo boolean not null default true,
Discount numeric(19,4) not null default 0
);
create table catalog_promosimpleitem (
PromoSimpleID int references catalog_promosimple (PromoSimpleID),
ItemID int references catalog_items (ItemID)
);

so eg. I've

1,true
2,false

1,1,10
1,1,20
2,1,0
2,1,5

when I'm looking for ItemID=1 the query should return:
ItemID,Discount
1,20
and skip the entries with IsPromo=false

If I've just

2,false

2,1,0
2,1,5

the query should return:
ItemID,Discount
2,null

If there are no items at all in catalog_promosimpleitem (but there
is a corresponding one in catalog_items eg. ItemID=5)
the query should return:
ItemID,Discount
5,null

select i.ItemID as _ItemID, i.Code as _Code, i.ISBN as _ISBN,
i.CodeAlt as _CodeAlt, i.Name as _Name,
i.ListPrice as _ListPrice,
DiscountedPrice(
i.qty, i.StatusID, max(pi.Discount),
p.Percent, p.OnListPrice, p.Vendible, p.OnStock,i.ListPrice,
ip.Price )
as _DiscountedPrice,
i.valIva as _Tax,
i.StatusID as _StatusID, i.qty as _qty, b.Name as _Brands,
i.BrandID as _BrandID, i.Authors as _Authors,
b.Delivery as _Delivery,
extract(year from i.dataPub) as _YearPub,
s.FamID as _FamID, st.Name as _SName from catalog_items i
join catalog_categoryitem s on i.ItemID=s.ItemID
join catalog_item_status st on st.StatusID=i.StatusID
left join catalog_brands b on b.BrandID=i.BrandID
left join catalog_itemprice ip on ip.ItemID=i.ItemID
and ip.DiscountClass=10
left join catalog_promosimpleitem pi on pi.ItemID=i.ItemID
left join catalog_promosimple p on
pi.PromoSimpleID=p.PromoSimpleID and p.PromoStart<now() and
p.PromoEnd>=now() and p.IsPromo=true where i.ItemID=102020
group by i.ItemID, i.Code, i.ISBN, i.CodeAlt,
i.Name, i.ListPrice, i.qty, i.StatusID, p.Percent, p.OnListPrice,
p.Vendible, p.OnStock, ip.Price, i.valIva, b.Name, i.BrandID,
i.Authors, i.dataPub, s.FamID, st.Name, b.Delivery

--
Ivan Sergio Borgonovo
http://www.webthatworks.it