help - JOIN problem

note: I also posted this in mailing.database.sql-general


http://groups.google.com/groups?hl=en&group=mailing.database.sql-general&selm=b2f93c8c.0204151045.57840ed9%40posting.google.com

Hi all, I have 3 tables; USER, ACCESS_PATH, and ACCESS (All of the
generation scripts are at the end).  USER contains users information,
ACCESS_PATH contains a URL and an ID, and ACCESS contains USER ids and
ACCESS ids I am basically trying to adminster my home website with access
levels.  So user1 can see some stuff, and user2 can see some other
stuff, while I (admin) can see everything. Here's my problem I'd like to have a query that does a left join on
the cartesian product of USER and ACCESS, thus showing me what users
do and do not have access to, so when a NULL value comes up for some
column ex:
'enabled', the user does not have access otherwise they do.

Any help appreciated,
Keith

---------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ACCESS_ACCESS_PATH]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ACCESS] DROP CONSTRAINT FK_ACCESS_ACCESS_PATH
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_ACCESS_USER]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ACCESS] DROP CONSTRAINT FK_ACCESS_USER
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ACCESS]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[ACCESS]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ACCESS_PATH]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[ACCESS_PATH]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[USER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USER]
GO

CREATE TABLE [dbo].[ACCESS] ([user_id] [int] NOT NULL ,[access_id] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ACCESS_PATH] ([access_id] [int] IDENTITY (1, 1) NOT NULL ,[path] [varchar] (256) COLLATE
SQL_Latin1_General_CP1_CI_ASNULL 
 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[USER] ([user_id] [int] IDENTITY (1, 1) NOT NULL ,[fname] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_ASNULL ,[lname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACCESS] WITH NOCHECK ADD CONSTRAINT [PK_ACCESS] PRIMARY KEY  CLUSTERED (    [user_id],
[access_id]) ON [PRIMARY] 
 
GO

ALTER TABLE [dbo].[ACCESS_PATH] WITH NOCHECK ADD CONSTRAINT [PK_ACCESS_PATH] PRIMARY KEY  CLUSTERED (    [access_id])
ON[PRIMARY] 
 
GO

ALTER TABLE [dbo].[USER] WITH NOCHECK ADD CONSTRAINT [PK_USER] PRIMARY KEY  CLUSTERED (    [user_id])  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ACCESS] ADD CONSTRAINT [FK_ACCESS_ACCESS_PATH] FOREIGN KEY (    [access_id]) REFERENCES
[dbo].[ACCESS_PATH](    [access_id]),CONSTRAINT [FK_ACCESS_USER] FOREIGN KEY (    [user_id]) REFERENCES [dbo].[USER] (
 [user_id])
 
GO