Re: CASE SELECT syntax

On Thu, 15 Jan 2004, Bruno Wolff III wrote:

> On Thu, Jan 15, 2004 at 18:23:47 +0100,
> Marco Lazzeri <marcomail(at)noze(dot)it> wrote:
> > What about a SELECT syntax like that?
> >
> > SELECT
> > CASE WHEN bar.foo_id IS NULL THEN bar.*
> > ELSE foo.*
> > FROM foo, bar
> >
> > Anyway, I need a SELECT query that gets data from a FIRST TABLE if a
> > specific value in a SECOND TABLE is NULL or from SECOND TABLE itself if
> > the value IS NOT NULL.
> >
> > Any suggestion?
>
> You almost certainly want some join condition between foo and bar.
> You can't actually use *. You will need a CASE for each column.
> CASE statements need and END.

Alternatively (though it fails to use the 'CASE' syntax) would be to use
COALESCE. Something like:

SELECT COALESCE((SELECT foo_id FROM table2 WHERE ...),
(SELECT bar_id FROM table1 WHERE ...));

HTH
-frank