Hi Raymond,
From a strictly relational viewpoint, this is as easy as
(select users.uid, apps.appcode from apps, users)
except
(select canaccess.uid, canaccess.appcode from canaccess)
In english, that's the cartesian product of all users' uid and apps appcode minus the known set of user+app allowed
use. Of course, for any non-trivial dataset, most of us would baulk at using a cartesian product because the
performancewould probably be a killer or at least need careful thought.
It might suit you ... otherwise you'll need to go for the correlated subquery approach using not exists.
Ciao
Fuzzy
:-)
Raymond O'Donnell wrote:
> 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.
>