Many-to-many problem

["Raymond O'Donnell" <rod@xxxxxx>]

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@xxxxxx

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general