2 left join taking in too many records, 1 join and 1 left join too few was: join

first, sorry for the subject.
I started editing the body and I forgot the subject looked nearly
meaningless.

I solved this using a view on 
catalog_promosimpleitem
and
catalog_promosimple
that just take out the record I'm interested into.
If no record, the left join on the view should make it work anyway
as expected.

I still don't like the overall solution, but at least it makes a
more reasonable starting point to clean some mess and having a list
of IsPromo around ready will come handy in other places.

On Tue, 17 Mar 2009 22:40:08 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> 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