Full self join problem

Hi I have the following table
CREATE TABLE [Sales] (
[StoreID] [int] NOT NULL ,
[CatName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[WEDate] [smalldatetime] NOT NULL ,
[Best_Sales] [real] NOT NULL ,
[Corrected_Sales] [real] NOT NULL ,
[Total_Waste] [real] NOT NULL ,
[Availability] AS (case when ([Corrected_Sales] > 0 and ([Best_Sales]
> 0)) then ([Best_Sales] / [Corrected_Sales] * 100) else 0 end) ,
[Waste] AS (case when ([Total_Waste] > 0 and ([Best_Sales] > 0)) then
([Total_Waste] / [Best_Sales] * 100) else 0 end) ,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreID],
[CatName],
[WEDate]
) ON [PRIMARY] ,
CONSTRAINT [FK_Sales_Stores] FOREIGN KEY
(
[StoreID]
) REFERENCES [Stores] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
The table holds sales records of a particular product catergory, in a
particular store in a particular week.
One week a catergory may be present which is not present in all weeks.
I would like to be able to compare sales of one week against another as
shown in the following example
Week, Cat, Store, Sales, Week, Cat, Store, Sales
1, Coat, 50, 50, 2, coat, 50, 10
1 Hat, 50, 10, null, null, null, null
null, null, null, null, 2, Gloves, 50, 10
I have tried many different queries using full, left, right joins and
unions but i always seem to be only to get the result of an inner join.
here is one example i have tried
select a.catname from sales a
full join MandS.dbo.Sales b
on b.catname = a.catname
and b.weDate = '2006-09-23'
and b.storeid = 259
where a.weDate = '2006-09-16'
and a.storeid = 259
any help would be greatly appreciated.
MattOn 28 Sep 2006 11:37:39 -0700, Matt S wrote:
(snip)
>here is one example i have tried
>select a.catname from sales a
>full join MandS.dbo.Sales b
> on b.catname = a.catname
> and b.weDate = '2006-09-23'
> and b.storeid = 259
>where a.weDate = '2006-09-16'
>and a.storeid = 259
Hi Matt,
The inclusion of columns from table a in the WHERE negates the effect of
the outer join - if a row from b was retained though there were no
matching values in a, the a columns are NULL, and the WHERE will discard
the row.
Try either
FROM (SELECT '
FROM sales
WHERE weDate = '20060916'
AND storeid = 259) AS a
FULL JOIN (SELECT '
FROM MandS.dbo.sales
WHERE weDate = '20060923'
AND storeid = 259) AS b
ON b.catname = a.catname
Or
FROM sales AS a
FULL JOIN MandS.dbo.Sales AS b
ON b.catname = a.catname
AND b.weDate = '20060923'
AND b.storeid = 259
AND a.weDate = '20060916'
AND a.storeid = 259
--
Hugo Kornelis, SQL Server MVP|||thanks hugo the 1st option worked.
> FROM (SELECT '
> FROM sales
> WHERE weDate = '20060916'
> AND storeid = 259) AS a
> FULL JOIN (SELECT '
> FROM MandS.dbo.sales
> WHERE weDate = '20060923'
> AND storeid = 259) AS b
> ON b.catname = a.catname
>
I don't know why this post took so long to apear over 6 hours, which is
why it it listed several times.
Thanks for your help