SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 outer join vs cross join question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ThePrisoner
Starting Member

18 Posts

Posted - 06/28/2007 :  07:01:21  Show Profile  Reply with Quote
hello

why don't the below queries give the same result ?
the second query doesn't include the records where there is no value in XpoMeasures for the spceific combination.

SELECT A.MarketSizeDesc, SUM(XpoMeasures.RxUnits) AS Expr1, A.ProdSizeDesc, A.ProductDesc,a.timeperioddesc
FROM XpoMeasures RIGHT OUTER JOIN
(select XpoMarketId,ProductDesc,XpoProdSizeId,ProdSizeDesc,
       XpoMarketSizeId,MarketSizeDesc,Timeperiodid,timeperioddesc 
 from XpoMarket,XpoProductsize,XpoMarketSize,xpotimeperiod) A
ON
XpoMeasures.XpoMarketId = A.XpoMarketId AND
XpoMeasures.XpoProductSizeId = A.XpoProdSizeId AND
XpoMeasures.XpoMarketSizeId = A.XpoMarketSizeId


GROUP BY A.MarketSizeDesc, A.ProdSizeDesc, A.ProductDesc,a.timeperioddesc




SELECT     XpoMarket.ProductDesc, XpoMarketSize.MarketSizeDesc, XpoProductsize.ProdSizeDesc, SUM(XpoMeasures.RxValues) AS Expr1, 
                      XpoTimePeriod.TimePeriodDesc
FROM         XpoProductsize LEFT OUTER JOIN
                      XpoMeasures ON XpoProductsize.XpoProdSizeId = XpoMeasures.XpoProductSizeId RIGHT OUTER JOIN
                      XpoMarketSize ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId RIGHT OUTER JOIN
                      XpoTimePeriod ON XpoMeasures.TimePeriodId = XpoTimePeriod.TimePeriodId RIGHT OUTER JOIN
                      XpoMarket ON XpoMeasures.XpoMarketId = XpoMarket.XpoMarketId
GROUP BY XpoMarket.ProductDesc, XpoMarketSize.MarketSizeDesc, XpoProductsize.ProdSizeDesc, XpoTimePeriod.TimePeriodDesc

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/28/2007 :  08:10:49  Show Profile  Visit nr's Homepage  Reply with Quote
This'll probably show you what's happening - basically the row has to be there for the right join.
You in fact have an inner join.

create table #a (i int)
create table #b (i int)
create table #c (i int)
insert #a select 1
insert #a select 2
insert #b select 1
insert #c select 1

select *
from #a
left join #b
on #a.i = #b.i

select *
from #a left join #b
on #a.i = #b.i

select *
from #a
left join #b
on #a.i = #b.i
left join #c
on #b.i = #c.i

select *
from #a
left join #b
on #a.i = #b.i
right join #c
on #b.i = #c.i


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ThePrisoner
Starting Member

18 Posts

Posted - 06/28/2007 :  08:37:43  Show Profile  Reply with Quote
thanks for your help
so I suppose the cartesian product is the only solution ?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/28/2007 :  09:29:35  Show Profile  Visit nr's Homepage  Reply with Quote
Unless you can turn everything into left joins.
I never use right joins as I think it makes queries more difficult to understand.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ThePrisoner
Starting Member

18 Posts

Posted - 06/28/2007 :  10:53:48  Show Profile  Reply with Quote
it's a star schema, so I don't think it's possible to change all to left joins
Go to Top of Page

ThePrisoner
Starting Member

18 Posts

Posted - 06/28/2007 :  11:43:04  Show Profile  Reply with Quote
i solved the problem with a CTE :

with mycte
as
(
SELECT XpoMarketSize.XpoMarketSizeId, XpoMarketSize.MarketSizeDesc, XpoProductsize.XpoProdSizeId, XpoProductsize.ProdSizeDesc,
XpoMarket.ProductDesc, XpoMarket.XpoMarketId
FROM XpoMarketSize CROSS JOIN
XpoProductsize CROSS JOIN
XpoMarket
WHERE (XpoMarketSize.DatasetName = 'hdaaam04') AND (XpoMarket.ProductDesc IN ('nexiam', 'pantozol', 'zurcale'))
)

select mycte.productdesc,mycte.MarketSizeDesc,mycte.ProdSizeDesc,b.TimePeriodDesc,b.DoctorSubTerritory,b.rxrcount
from mycte
left outer join


(SELECT XpoTimePeriod.TimePeriodDesc, XpoProductsize.XpoProdSizeId, XpoMarketSize.XpoMarketSizeId, XpoGeography.DoctorSubTerritory, COUNT(DISTINCT XpoMeasures.GeographyId) AS RxrCount
FROM XpoTimePeriod INNER JOIN
XpoMeasures ON XpoTimePeriod.TimePeriodId = XpoMeasures.TimePeriodId INNER JOIN
XpoProductsize ON XpoMeasures.XpoProductSizeId = XpoProductsize.XpoProdSizeId INNER JOIN
XpoMarketSize ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId INNER JOIN
XpoGeography ON XpoMeasures.GeographyId = XpoGeography.GeographyId
WHERE (XpoTimePeriod.TimePeriodDesc = 'month 200704') AND (XpoGeography.DoctorSubTerritory = '321-brugge')
GROUP BY XpoTimePeriod.TimePeriodDesc, XpoProductsize.XpoProdSizeId, XpoMarketSize.XpoMarketSizeId, XpoGeography.DoctorSubTerritory
) b
on mycte.XpoMarketSizeId = b.XpoMarketSizeId
and mycte.XpoProdSizeId = b.XpoProdSizeId
order by mycte.productdesc
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000