From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Help with UNION query
Date: Sun, 4 Sep 2005 19:14:25 +0200
User-Agent: KMail/1.8.91
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart3504754.E1qu4LKVqi";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200509041914.33245.andreak@officenet.no>

--nextPart3504754.E1qu4LKVqi
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all, I have the followin query:

SELECT g.id
from onp_group g
where g.groupname IN
	(SELECT ug.groupname from onp_user_group ug WHERE ug.username =3D 'andreak=
')
UNION (SELECT child_id
	FROM onp_group_children
	WHERE group_id IN
		(SELECT g.id from onp_group g
	where g.groupname IN (SELECT ug.groupname from onp_user_group ug WHERE=20
ug.username =3D 'andreak')));

Any ideas on how I can simplyfy this query so that I only need to provide=20
ug.username=3D'andreak' once? I would much rather prefer a query which does=
n't=20
involve UNION or SUB-SELECTS.

Here are the table-definitions:

CREATE TABLE onp_group(
id integer PRIMARY KEY REFERENCES onp_entity(id) on delete cascade,
p_id integer REFERENCES onp_group(id) on delete cascade,
groupname varchar NOT NULL unique
);

CREATE TABLE onp_group_children(
group_id integer NOT NULL REFERENCES onp_group(id),
child_id integer NOT NULL REFERENCES onp_group(id),
UNIQUE(group_id, child_id)
);

CREATE TABLE onp_user_group(
groupname varchar not null REFERENCES onp_group(groupname) ON DELETE CASCAD=
E=20
ON UPDATE CASCADE,
username varchar not null REFERENCES onp_user(username) ON DELETE CASCADE O=
N=20
UPDATE CASCADE,
primary key(groupname, username)
);

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Sk=F8yen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+

--nextPart3504754.E1qu4LKVqi
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQBDGyt5UopImDh2gfQRAn/cAJ4jQyDoIxvXn7AbYwWVGhvCRDFgcgCffzGJ
nJGOI8MBkjuAmbc1GUCO5pE=
=HTrC
-----END PGP SIGNATURE-----

--nextPart3504754.E1qu4LKVqi--