From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | rod(at)iol(dot)ie |
Cc: | 'PostgreSQL' <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Many-to-many problem |
Date: | 2010-03-18 23:08:40 |
Message-ID: | 4BA2B278.1060502@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stuart McGraw | 2010-03-18 23:14:30 | Re: building a c function |
Previous Message | Vikram Patil | 2010-03-18 22:57:43 | AIX postgresql error |