Re: [GENERAL] SELECT Question

[Jeffrey Melloy]

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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly