Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 6E34C475913 for ; Tue, 16 Apr 2002 11:41:31 -0400 (EDT) Received: from jupiter.hub.org (localhost [127.0.0.1]) by jupiter.hub.org (8.12.2/8.12.2) with ESMTP id g3GFfUp7081462 for ; Tue, 16 Apr 2002 10:41:33 -0500 (CDT) (envelope-from news@jupiter.hub.org) Received: (from news@localhost) by jupiter.hub.org (8.12.2/8.12.2/Submit) id g3GEAUU9048952 for pgsql-sql@postgresql.org; Tue, 16 Apr 2002 09:10:30 -0500 (CDT) From: chief_87@yahoo.com (KeithR) X-Newsgroups: comp.databases.postgresql.sql Subject: help - JOIN problem Date: 16 Apr 2002 07:10:17 -0700 Organization: http://groups.google.com/ Lines: 106 Message-ID: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200204/202 X-Sequence-Number: 7466 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_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[USER] ( [user_id] [int] IDENTITY (1, 1) NOT NULL , [fname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [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