Re: Using case or if to return multiple rows

> >
> >
> > select case when t3.date='xxxx' then
> > select * from table1
> > else
> > select * from table 2
> > from table3 t3 where t3.date='xxxxx'
> >
> > Problem is that I have to do it in Plain SQL.
>
> you problem is not quite clear.
> do you want to output all rows from table1 (or table2)
> for each row of table3?
> or does table2 only have one row with date='xxxxx' ?
> is 'xxxx' the same date as 'xxxxx' in your example?
> if so, how can table2's columns be selected.
>
> are you looking for something like:
>
> select * from table1
> where (select date from table3)='xxxx'
> UNION ALL
> select * from table2
> where (select date from table3)<>'xxxx'
>

maybe little bit more readable form:

select *
from table1
where exists (select 1 from table3 where date = 'xxxx')
union all
select *
from table2
where exists (select 1 from table3 where date = 'xxxx');

it's possible with one big disadvantage. This query will do seq scan
both tables and it can be slow on big tables.

Regards
Pavel Stehule