From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | How to force select to return exactly one row |
Date: | 2010-06-21 19:08:00 |
Message-ID: | 52BE6A1088554C0497BF37E4467979EF@andrusnotebook |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Martin | 2010-06-21 19:14:28 | Re: How to force select to return exactly one row |
Previous Message | John R Pierce | 2010-06-21 18:53:16 | Re: pgpool |