MS SQL Server - Full self join problem

Author

Full self join problem

Matt S

2006-10-24, 6:32 pm

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 ,
& #91;Corrected_Sales]
[real] NOT NULL ,
[Total_Waste] [real] NOT NULL ,
[Availability] AS (case when (& #91;Corrected_Sales]
> 0 and (& #91;Best_Sales][colo
r=darkred]
> 0)) then ([Best_Sales] / & #91;Corrected_Sales]
* 100) else 0 end) ,[/color]
[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 & #91;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, Sa
les, Week, Cat, Stor
e, Sales
1, Coat, 50, 50, 2, coat, 50, 10
1 Hat, 50, 10, null, null, nul
l, null
null, null, null, nu
ll, 2, Gloves, 50, 1
0

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

Hugo Kornelis

2006-10-24, 6:32 pm

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

Matt S

2006-10-24, 6:33 pm

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

Sponsored Links