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
1Hat,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.
Matt
On 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