Thread: Many-to-many problem

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

On 03/18/2010 03:50 PM, Raymond O'Donnell wrote:
>
> 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.

Is this what you want?

INSERT INTO apps VALUES ('app1', 'appname1'), ('app2', 'appname2'),
('app3', 'appname3');
INSERT INTO users VALUES ('uid1', 'ln1', 'fn1'), ('uid2', 'ln2', 'fn2'),
('uid3', 'ln3', 'fn3');
INSERT INTO canaccess VALUES ('uid1', 'app1', 'pwd'), ('uid1', 'app2',
'pwd'), ('uid2', 'app2', 'pwd'), ('uid2', 'app3', 'pwd'), ('uid3',
'app3', 'pwd');

SELECT uid, appcode FROM users, apps EXCEPT SELECT uid, appcode FROM
canaccess;
 uid  | appcode
------+---------
 uid1 | app3
 uid2 | app1
 uid3 | app1
 uid3 | app2
(4 rows)

It could potentially get pretty ugly performance-wise as the first half
of that query is a Cartesian join.

HTH,

Joe

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.
>

On 18/03/2010 23:18, Grant Allen wrote:
> 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)

Joe & Grant, thanks for your replies... I knew it had to be easy....
that'll teach me to finish working earlier while the brain is clear!

The cartesian product is no problem in my case, as the size of both
tables will never get any bigger than a few dozen rows each.

> It might suit you ... otherwise you'll need to go for the correlated
> subquery approach using not exists.

Can I ask for an example of this?

Thanks,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie