Skip site navigation (1) Skip section navigation (2)

Re: Join question

From: Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org>
To: "Williams, Travis L, NEO" <tlw(at)att(dot)com>
Cc: "Thomas A(dot) Lowery" <tlowery(at)stlowery(dot)net>,<pgsql-general(at)postgresql(dot)org>
Subject: Re: Join question
Date: 2003-08-29 05:31:55
Message-ID: 1A269922-D9E2-11D7-8AF9-000393C78AC0@visualdistortion.org (view raw or flat)
Thread:
Lists: pgsql-general
On Thursday, August 28, 2003, at 09:03  PM, Williams, Travis L, NEO 
wrote:
>> I have a table1 with 2 col (a & b) where b can sometimes be null. I
>> need a query that if B is null I get back the contents of A.. but if B
>> is not null I do a "select d from table2 where d like '%b%'"  There is
>> nothing to join between table1 & table2 (unless you can join on likes
>>

You can do something like this, but I can't promise any great 
performance:

select case when b is null
                      then a
                      else (select d from table2 where d ~* b) end as 
value
from    table1;

jmelloy=# select * from table1;
  a |  b
---+------
  1 |
  2 |
  3 |
  4 | for
  5 | asdf
  6 | coo
(6 rows)

jmelloy=# select * from table2;
     d
----------
  forsythe
  manasdf
  cool
(3 rows)

jmelloy=# select case when b is null then a::varchar else
jmelloy-# (select d from table2 where d ~* b) end as value
jmelloy-# from table1;
   value
----------
  1
  2
  3
  forsythe
  manasdf
  cool
(6 rows)


In response to

pgsql-general by date

Next:From: Williams, Travis L, NEODate: 2003-08-29 05:36:28
Subject: Re: Join question
Previous:From: Tom LaneDate: 2003-08-29 04:25:17
Subject: Re: left outer join terrible slow compared to inner join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group