pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
help - JOIN problem
2+ messages / 2 participants
[nested] [flat]

* help - JOIN problem
@ 2002-04-16 14:10  KeithR <chief_87@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: chief_87@yahoo.com @ 2002-04-16 14:10 UTC (permalink / raw)
  To: pgsql-sql

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

http://groups.google.com/groups?hl=en&group=mailing.database.sql-general&selm=b2f93c8c.02041...

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: help - JOIN problem
@ 2002-04-17 04:32  Josh Berkus <josh@agliodbs.com>
  parent: KeithR <chief_87@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Josh Berkus @ 2002-04-17 04:32 UTC (permalink / raw)
  To: chief_87@yahoo.com; pgsql-sql

Keith,

>   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

No, your problem is that you are using Microsoft SQL Server and not
PostgreSQL.  This is a PostgreSQL list.  If you insist on using MS SQL
Server, your troubles are your own.  I'd suggest that you join a list
for the database you are actually using.

-Josh Berkus




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2002-04-17 04:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-16 14:10 help - JOIN problem KeithR <chief_87@yahoo.com>
2002-04-17 04:32 ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox