G'night all,
I'm being driven nuts by an SQL problem which I think ought to be
simple, but I can't see the answer.
I have two tables related many-to-many via a third - they describe a set
of users, a set of applications and which users have been granted access
to which applications. What I want is to create a view which lists all
users and the applications to which they *don't* have access.
CREATE TABLE apps
(
appcode character varying(16) NOT NULL,
appnameshort character varying(32) NOT NULL,
...
CONSTRAINT apps_pk PRIMARY KEY (appcode)
);
CREATE TABLE users
(
uid character varying(16) NOT NULL,
surname character varying(32) NOT NULL,
firstname character varying(32) NOT NULL,
...
CONSTRAINT users_pkey PRIMARY KEY (uid)
);
CREATE TABLE canaccess
(
uid character varying(16) NOT NULL,
appcode character varying(16) NOT NULL,
pwd character varying(16) NOT NULL,
CONSTRAINT canaccess_pk PRIMARY KEY (uid, appcode),
CONSTRAINT appcode_fk FOREIGN KEY (appcode)
REFERENCES apps (appcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT uid_fk FOREIGN KEY (uid)
REFERENCES users (uid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
I can do it easily enough for one user; my problem is doing it for all
users in one fell swoop.
I'm sure this is a very common problem, but I just can't see the
solution, so any pointers would be greatly appreciated.
Many thanks in advance....
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
