Search Postgresql Archives

Re: How to force select to return exactly one row

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Andrus" <kobruleht2@xxxxxx> wrote:

> 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 ?

It's a bit difficult to decipher what you're looking for
(what do you mean by "empty row"?), but you may want to try
something along the lines of:

| SELECT v.primarykey, ko.somecolumns
|   FROM (VALUES ('someprimarykeyvalue')) AS v (primarykey)
|   LEFT JOIN ko ON v.primarykey  = ko.primarykey
|   LEFT JOIN t1 ON t1.primarykey = ko.t1foreignkey
|   [...]
|   LEFT JOIN tn ON tn.primarykey = ko.tnforeignkey;

Whether that suits your needs depends very much on the data
structure and the tools you use.

Tim


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux