Re: Many-to-many problem

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