Thread: 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 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
2 left join taking in too many records, 1 join and 1 left join too few was: join
From
Ivan Sergio Borgonovo
Date:
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