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(at)webthatworks(dot)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