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
referencescatalog_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.BrandIDas _BrandID, i.Authors as _Authors,b.Delivery as _Delivery,extract(year from i.dataPub) as
_YearPub,s.FamIDas _FamID, st.Name as _SName from catalog_items ijoin catalog_categoryitem s on i.ItemID=s.ItemIDjoin
catalog_item_statusst on st.StatusID=i.StatusIDleft join catalog_brands b on b.BrandID=i.BrandIDleft join
catalog_itempriceip on ip.ItemID=i.ItemIDand ip.DiscountClass=10left join catalog_promosimpleitem pi on
pi.ItemID=i.ItemIDleftjoin catalog_promosimple p on pi.PromoSimpleID=p.PromoSimpleID and p.PromoStart<now() and
p.PromoEnd>=now()and p.IsPromo=true where i.ItemID=102020group 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