Re: How to force select to return exactly one row

2010/6/21 Andrus <kobruleht2(at)hot(dot)ee>:
> 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';
>

The reason this won't return a row if there is no matching PK is that
the WHERE clause is applied after all the joins to filter the overall
result set.

So to get what you want, you would need to re-arrange that to something like:

SELECT original_query.* FROM
( Original query including WHERE clause ) AS original_query
RIGHT JOIN (SELECT 1) AS one_row ON true;

Regards,
Dean