How about: SELECT * from ( SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue' UNION ALL SELECT default_value ) LIMIT 1; -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andrus Sent: Tuesday, 22 June 2010 5:08 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: How to force select to return exactly one row Autogenerated select statement contains 0 .. n left joins: SELECT somecolumns FROM ko LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey='someprimarykeyvalue'; This select can return only 0 or 1 rows depending if ko row with primary key 'someprimarykeyvalue' exists or not. Problem: if there is no searched primary key row in ko database, select should also return empty row. To get this result I added right join: SELECT somecolumns FROM ko RIGHT JOIN (SELECT 1) _forceonerow ON true LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey ... LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue'; but it still does not return row if primary key row 'someprimarykeyvalue' does not exist. How to force this statement to return one row always ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
