full-joins, subqueries & composite keys

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

Old Forums

CLOSED - General SQL Server

full-joins, subqueries & composite keys

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-11 : 08:12:51

DB King writes "I have a slight problem with a full-join query which is based upon sub-queries.
I have two tables called Activity and Store_activity which are defined:

CREATE TABLE [dbo].[Activity] (
[Activity_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Timestamp] [timestamp] NULL ,
[Create_Date] [datetime] NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Store_Activity] (
[Store_ID] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
[Activity_ID] [int] NOT NULL ,
[Timestamp] [timestamp] NULL ,
[Create_Date] [datetime] NULL ,

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Activity] WITH NOCHECK ADD
CONSTRAINT [PK_Activity] PRIMARY KEY CLUSTERED
(
[Activity_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Store_Activity] WITH NOCHECK ADD
CONSTRAINT [PK_Store_Activity] PRIMARY KEY CLUSTERED
(
[Store_ID],
[Activity_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Activity] ADD
CONSTRAINT [DF_Activity_Create_Date] DEFAULT (getdate()) FOR [Create_Date]
GO

CREATE INDEX [idx_Activity_release] ON [dbo].[Activity]([Release]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Store_Activity] ADD
CONSTRAINT [DF_Store_Activity_Create_Date] DEFAULT (getdate()) FOR [Create_Date]
GO

CREATE INDEX [idx_Store_Activity_activity_id] ON [dbo].[Store_Activity]([Activity_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[Store_Activity] ADD
CONSTRAINT [FK_Store_Activity_Activity] FOREIGN KEY
(
[Activity_ID]
) REFERENCES [dbo].[Activity] (
[Activity_ID]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_Store_Activity_Store] FOREIGN KEY
(
[Store_ID]
) REFERENCES [dbo].[Store] (
[Store_ID]
) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION
GO

Within the activity table I define all the activities based upon a release. So for a release called Release 5.0 I have 138 activities. I also have a table called Stores which I use to relate the activites to to form Store_activity. So, I have 542 Stores which if I do a full join, I should get 138 x 542 store_activities = 74796.

When I try a evaluate the mismatches for all stores I get only 62634 with this query:

select sa.store_id, sa.activity_id as store_activity_id, a.activity_id
from (select * from activity where release = '5.0') as a
full join (select store_id, activity_id from store_activity where activity_id in (select activity_id from activity where release = '5.0')) as sa
on a.activity_id = sa.activity_id
order by sa.store_id

However, when I specify that I am only interested in one store (0002) SQL Server returns the mismatches aswell :
and store_id = '0002'

select sa.store_id, sa.activity_id as store_activity_id, a.activity_id
from (select * from activity where release = '5.0') as a
full join (select store_id, activity_id from store_activity where activity_id in (select activity_id from activity where release = '5.0')

and store_id = '0002') as sa

on a.activity_id = sa.activity_id
order by sa.store_id"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources