Subject: Re: Using case or if to return multiple rows
From: Ragnar <gnari@hive.is>
To: Ashish Karalkar <ashish.karalkar@info-spectrum.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
References: <005401c7c450$3d6670e0$170211ac@LIONKING.COM>
Content-Type: text/plain
Date: Thu, 12 Jul 2007 09:52:08 +0000
Message-Id: <1184233928.5778.117.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On fim, 2007-07-12 at 12:15 +0530, Ashish Karalkar wrote:
> I want to select data from two diffrent table based on third tables
> column
> somthing like:
>  
>  
> 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'

this might work if table1 and table2 have compatible schemas, and table3
only has 1 row, and table3.date is NOT NULL

if this is not what you are looking for, you need to be more clear.

gnari