Problem with UNION-queries - Mailing list pgsql-sql
From | Andreas Joseph Krogh |
---|---|
Subject | Problem with UNION-queries |
Date | |
Msg-id | 200711122127.14860.andreak@officenet.no Whole thread Raw |
List | pgsql-sql |
Hi all I have a somewhat furry solution to a problem for which I think there might be a better way to do it. The table looks like this (simplified for the sake of this example): drop table if exists test1; create table test1( id serial primary key, key varchar, username varchar, value varchar ); -- Insert test-data insert into test1(username, key, value) values('andreak', 'A', 'value1'); insert into test1(username, key, value) values('andreak', 'A', 'value2'); insert into test1(username, key, value) values('andreak', 'A', 'value3'); insert into test1(username, key, value) values('andreak', 'B', 'value2'); insert into test1(username, key, value) values('andreak', 'B', 'value3'); insert into test1(username, key, value) values('andreak', 'B', 'value4'); insert into test1(username, key, value) values('andreak', null, 'value3'); insert into test1(username, key, value) values('andreak', null, 'value4'); insert into test1(username, key, value) values('andreak', null, 'value5'); For the sake of this example the test-case is greatly simplified, so I have the following query to give me all rows for value='A' and value='B' select t.username, t.key, t.value from test1 t where t.key = 'A' UNION select t.username, t.key, t.value from test1 t where t.key = 'B' username | key | value ----------+-----+--------andreak | A | value1andreak | A | value2andreak | A | value3andreak | B | value2andreak | B | value3andreak | B | value4 (6 rows) Again, I know there are other, better, ways to accomplish that with this simple schema, but again it's for the sake of the example. The important thing here is that it's 2 UNION-queries providing the result. Now - what I'm trying to accomplish is getting the following result-set:username | key | value ----------+-----+--------andreak | A | value1andreak | A | value2andreak | A | value3andreak | B | value2andreak | B | value3andreak | B | value4andreak | | value5 (7 rows) That is - I want all rows with username='andreak' AND (key IS NULL) where the "value" is not in the previous result (not part of the other UNION-queries). The hard, and important, part is that the resulting rows' "value" must not exist in the "value"-column for any previous rows. Here is one way I figured out how to do it: select t.username, t.key, t.value from test1 t where t.key = 'A' UNION select t.username, t.key, t.value from test1 t where t.key = 'B' UNION select t.username, t.key, t.value from test1 t where t.value NOT IN ( select value from ( select t.username, t.key, t.valuefrom test1 t where t.key = 'A' UNION select t.username, t.key, t.value from test1 t where t.key = 'B' ) tmp1 ) ; Given that my real schema is way more complex I'm looking for a solution which doesn't involve issuing the NOT IN (original UNION-query) as it is a rather heavy query. Does anybody have a better approach to this problem? -- Andreas Joseph Krogh <andreak@officenet.no> Senior Software Developer / Manager ------------------------+---------------------------------------------+ OfficeNet AS | The most difficult thing in the world is to | Karenslyst Allé 11 | know how to do a thing and to watch | PO. Box 529 Skøyen | somebody else doing it wrong, without | 0214 Oslo | comment. | NORWAY | | Tlf: +47 24 15 38 90 | | Fax: +47 24 15 38 91 | | Mobile: +47 909 56 963 | | ------------------------+---------------------------------------------+