Thread: Help with UNION query
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 = 'andreak') UNION (SELECT child_idFROM onp_group_childrenWHERE group_id IN (SELECT g.id from onp_group gwhere g.groupname IN (SELECTug.groupname from onp_user_group ug WHERE ug.username = 'andreak'))); Any ideas on how I can simplyfy this query so that I only need to provide ug.username='andreak' once? I would much rather prefer a query which doesn't 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 CASCADE ON UPDATE CASCADE, username varchar not null REFERENCES onp_user(username) ON DELETE CASCADE ON UPDATE CASCADE, primary key(groupname, username) ); -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ 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øyen | 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 | | ------------------------+---------------------------------------------+
Replying to my self... Here's a somewhat simpler version without the IN-queries: SELECT g.id from onp_group g, onp_user_group ug where g.groupname = ug.groupname AND ug.username = 'andreak' UNION SELECT gc.child_id FROM onp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = 'andreak'; But I would very much appreciate if someone manages to rewrite this query so that it doesn't specify ug.username='anderak' twice. -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ 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øyen | 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 | | ------------------------+---------------------------------------------+
On Sunday 04 September 2005 20:08, Andreas Joseph Krogh wrote: > Replying to my self... Again... I managed to "reduse" my query to the following: SELECT gr.groupname FROM onp_group gr, onp_user u WHERE gr.id IN( SELECT g.id FROMonp_group g, onp_user_group ug WHERE g.groupname = ug.groupname AND ug.username = u.username UNION SELECT gc.child_id FROMonp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = u.username) AND u.username = 'andreak'; But "EXPLAIN ANALYZE" tells me it's about 8x slower than this one: SELECT gr.groupname FROM onp_group gr WHERE gr.id IN( SELECT g.id from onp_group g, onp_user_group ug where g.groupname = ug.groupname AND ug.username = 'andreak' UNION SELECT gc.child_id FROM onp_group_children gc, onp_group g, onp_user_group ug WHERE gc.group_id = g.id AND g.groupname = ug.groupname AND ug.username = 'andreak' ); So, the question stands: any idea on how to rewrite the lower wuery to only specify "ug.username='andreak'" once? -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager gpg public_key: http://dev.officenet.no/~andreak/public_key.asc ------------------------+---------------------------------------------+ 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øyen | 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 | | ------------------------+---------------------------------------------+
On Sun, Sep 04, 2005 at 20:54:00 +0200, Andreas Joseph Krogh <andreak@officenet.no> wrote: > > So, the question stands: any idea on how to rewrite the lower wuery to only > specify "ug.username='andreak'" once? Why do you want to do that? This isn't going to help with performance and may actually hurt. We may be able to suggest some other way to accomplish your real objective if you told us what it was.