How to force select to return exactly one row

Поиск
Список
Период
Сортировка
От Andrus
Тема How to force select to return exactly one row
Дата
Msg-id 52BE6A1088554C0497BF37E4467979EF@andrusnotebook
обсуждение исходный текст
Ответы Re: How to force select to return exactly one row  (Martin <mgonzo@gmail.com>)
Re: How to force select to return exactly one row  (Brett Mc Bride <brett.mcbride@deakin.edu.au>)
Re: How to force select to return exactly one row  (Dean Rasheed <dean.a.rasheed@gmail.com>)
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: John R Pierce
Дата:
Сообщение: Re: pgpool
Следующее
От: Martin
Дата:
Сообщение: Re: How to force select to return exactly one row