From: Pavel Stehule <pavel.stehule@gmail.com>
To: Ragnar <gnari@hive.is>
Cc: Ashish Karalkar <ashish.karalkar@info-spectrum.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using case or if to return multiple rows
Date: Thu, 12 Jul 2007 13:25:55 +0200
Message-ID: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com> (raw)
In-Reply-To: <1184233928.5778.117.camel@localhost.localdomain>
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
<1184233928.5778.117.camel@localhost.localdomain>
> >
> >
> > 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, gnari@hive.is, ashish.karalkar@info-spectrum.com
Subject: Re: Using case or if to return multiple rows
In-Reply-To: <162867790707120425p42e6de45k1342107418c80498@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox