Re: SELECT Question

Jeffrey,
second solution is a beauty... thanks a lot.

Alex

Jeffrey Melloy wrote:

> If I'm understanding you correctly, you can do something like:
>
> select cola,
> colb,
> exists
> (select 'x'
> from tableb
> where colc = colb)
> from tablea
>
> Since that has a subselect, you may get better performance with
> something like this:
> select cola,
> colb,
> case when colc is null
> then 'f' else 't' end as exists
> from table1 left join table2 on colb = colc;
>
> jmelloy=# create table table1(cola serial, colb char);
> NOTICE: CREATE TABLE will create implicit sequence 'table1_cola_seq'
> for SERIAL column 'table1.cola'
> CREATE TABLE
> jmelloy=# create table table2 (colc char);
> CREATE TABLE
> jmelloy=# insert into table1 (colb) values ('A');
> INSERT 1551538 1
> jmelloy=# insert into table1 (colb) values ('B');
> INSERT 1551539 1
> jmelloy=# insert into table1 (colb) values ('a');
> INSERT 1551540 1
> jmelloy=# insert into table2 values ('B');
> INSERT 1551541 1
> jmelloy=# select cola, colb, exists (select 'x' from table2 where colc
> = colb) from table1;
> cola | colb | ?column?
> ------+------+----------
> 1 | A | f
> 2 | B | t
> 3 | a | f
> (3 rows)
> jmelloy=# select cola, colb, case when colc is null then 'f' else 't'
> end as exists from table1 left join table2 on colb = colc;
> cola | colb | exists
> ------+------+--------
> 1 | A | f
> 2 | B | t
> 3 | a | f
> (3 rows)
> On Sunday, August 31, 2003, at 12:03 PM, Alex wrote:
>
>> Hi,
>>
>> I need to form a query where i can add some columns based on the result.
>>
>>
>> Table A
>> ColA, ColB
>> ----------
>> 1 A
>> 2 B
>> 3 A
>>
>> Table B
>> ColC
>> ----
>> A
>>
>> If A exists if would like the result back as
>> 1 A OK
>> 2 B NG
>> 3 A OK
>>
>> Is it possible to replace the value in the query ?
>>
>> Thanks
>> Alex
>>
>>
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>