[SQL] join

[Ivan Sergio Borgonovo]

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql