Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 outer join vs cross join question

Author  Topic 

ThePrisoner
Starting Member

18 Posts

Posted - 2007-06-28 : 07:01:21
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

12543 Posts

Posted - 2007-06-28 : 08:10:49
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 - 2007-06-28 : 08:37:43
thanks for your help
so I suppose the cartesian product is the only solution ?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-28 : 09:29:35
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 - 2007-06-28 : 10:53:48
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 - 2007-06-28 : 11:43:04
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
   

- Advertisement -