full-Joins, subqueries and 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

SQL Server 2000 Forums

Transact-SQL (2000)

full-Joins, subqueries and composite keys

Author

Topic

DB King
Starting Member

21 Posts

Posted - 2005-04-08 : 10:46:48

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 11:44:17

>>So, I have 542 Stores which if I do a full join, I should get 138 x 542 store_activities = 74796.

You are thinking of a CROSS JOIN, not a Full Join.

- Jeff

DB King
Starting Member

21 Posts

Posted - 2005-04-08 : 11:46:30

OK, how would I implement that ?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 12:04:18

if you want all rows in tableA cross joined with all rows in TableB, resulting in (TableA Count) X (TableB Count) rows, you say:

SELECT TableA.*, TableB.*
FROM TableA
CROSS JOIN TableB

Do you have books on-line? DId you try searching for Cross Join? I don't know if this solves your particular problem, but I wanted to make sure you were clear on what a FULL OUTER JOIN is, since it doesn't work the way you indicated.

- Jeff

DB King
Starting Member

21 Posts

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

I put the two sides of the queries into views to simplify the problem. And I have just run the following query with no success.

select
sa.store_id, sa.activity_id as store_activity_id,
a.activity_id

from
view_release_5_activities as a

cross join
view_release_5_store_activities as sa

where a.activity_id = sa.activity_id

order by sa.store_id

the views being:

CREATE VIEW view_release_5_store_activities
AS
select store_id, activity_id
from store_activity
where activity_id in
(select activity_id from view_release_5_activities)
GO

AND

CREATE VIEW view_release_5_activities
AS
select *
from activity
where release = '5.0'
GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 13:42:12

Again, not helping with your specific problem, but trying to help you understand how joins work, keep in mind that:

cross join
view_release_5_store_activities as sa
where a.activity_id = sa.activity_id

is exactly the same as:

inner join
view_release_5_store_activities as sa
on a.activity_id = sa.activity_id

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-08 : 13:44:40

how about some sample data and expected results. I don't know what you mean by "finding mismatches". Try to more clearly define your situation and the results you wish to return, and we'll be happy to help.

If you post the DDL for your Store table and INSERT statements for your sample data, along with detailed expectations of what you'd like returned, I guarantee you'll get an answer from someone within a half hour ...

(by the way -- unless you want us to trouble-shoot efficiency, you don't need to show us indexes and default value constraints ... just CREATE TABLE statements with relevant columns to this particular problem)

- Jeff

DB King
Starting Member

21 Posts

Posted - 2005-04-08 : 14:11:41

what i mean by mismatches is that:
I want to find all activities which are not associated to a store

Given each release consists of a group of stores by appending to the store_activity table.
Because this is a manual process, some people may have added stores to releases without associating all activities to a store.

DB King
Starting Member

21 Posts

Posted - 2005-04-08 : 14:15:13

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 ,
[Not_Required] [bit] NOT NULL ,
[Revised_Planned_Date] [datetime] NULL ,
[Revised_AM_PM] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Revised_Start_Time] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Revised_Finish_Time] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Actual_Date] [datetime] NULL ,
[Comments] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Fix] [int] NULL ,
[Go_Status] [bit] NULL ,
[Activity_Status_ID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Activity] (
[Activity_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Timestamp] [timestamp] NULL ,
[Create_Date] [datetime] NULL ,
[Number] [int] NULL ,
[Name] [nvarchar] (170) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [nvarchar] (800) COLLATE Latin1_General_CI_AS NULL ,
[Release] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Phase] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Default_Planned_Week] [numeric](18, 2) NOT NULL ,
[Drop_Dead_Date] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Critical_Path] [bit] NULL ,
[Team] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Area] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Applicable_Stores] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Central_Responsiblity] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Execution_Ownership] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Day] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[Duration] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Comments] [nvarchar] (800) COLLATE Latin1_General_CI_AS NULL ,
[Update_Ownership] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Online_User_ID] [int] NULL ,
[order_id] [smallint] NULL ,
[status_report] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[exceptions_report] [char] (1) COLLATE Latin1_General_CI_AS NULL ,
[visuailsation_name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[visuailsation_display] [bit] NULL ,
[visualisation_activity_id] [int] NULL
) ON [PRIMARY]
GO

DB King
Starting Member

21 Posts

Posted - 2005-04-08 : 14:30:25

A break-down of what the tables look like and what I want the query to return as below:

------------------------
Activity table
------------------------

actvity_id release
---------- -------
1 1
2 3
3 3
4 3
5 2
6 3

------------------------
Store Activity table
------------------------

store_id activity_id
-------- -----------
0002 2
0002 3
0002 1
0003 2
0003 3
0003 4

Now if I want to find out which Store are not associated with all activities within a given release I should end up with results like this@

store_id store_activity_id activity_id
-------- ----------------- -----------
0002 2 2
0002 3 3
0002 NULL 4
0002 NULL 6
0003 2 2
0003 3 3
0003 4 4
0003 NULL 6

from that i can elliminate the NULLs to be left with

store_id store_activity_id activity_id
-------- ----------------- -----------
0002 NULL 4
0002 NULL 6
0003 NULL 6

Does that make more sense ?

DB King
Starting Member

21 Posts

Posted - 2005-04-09 : 19:10:01

sorry if I sound funny but jsmith8858 it has been a bit more than 1/2 hr now ?!?!?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-09 : 19:50:48

quote:
Originally posted by DB King

sorry if I sound funny but jsmith8858 it has been a bit more than 1/2 hr now ?!?!?

Yeah, the posting has been slow today...

How does this work ?

SELECT
	s.Store_ID
	,sa.Activity_ID AS store_activity_id
	,a.Activity_ID
FROM
	(Store s CROSS JOIN Activity a)
	LEFT JOIN Store_Activity sa
		ON s.Store_ID = sa.Store_ID
		AND a.Activity_ID = sa.Activity_ID
WHERE
	a.Release = N'3'
	AND sa.Activity_ID IS NULL

rockmoose

DB King
Starting Member

21 Posts

Posted - 2005-05-18 : 11:55:07

jsmith8858 where are you ????

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-18 : 13:25:57

So in the last month You made some progress ???
Did You run the query ?, and it did not work...

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources