Re: Join question

Performace wise would I be better off just doing 2 query's.. i.e. select
a,b from table1.. then in perl I can check if b is not null and if is
isn't then I do a second query?

Travis

-----Original Message-----
From: Jeffrey Melloy [mailto:jmelloy(at)visualdistortion(dot)org]
Sent: Friday, August 29, 2003 12:32 AM
To: Williams, Travis L, NEO
Cc: Thomas A. Lowery; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Join question

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)