Обсуждение: SELECT Question

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

On Mon, 1 Sep 2003, 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 ?


Maybe something like one of these:
 select cola, colb, case when not exists(select 1 from table_b where
  table_b.colc=table_a.colb) then 'NG' else 'OK' end
 from table_a;

 select cola, colb, case when colc is null then 'NG' else 'OK' end
 from table_a left outer join table_b on (table_a.colb=table_b.colc);

 select cola, colb, case when (select count(*) from table_b where
  table_b.colc=table_a.colb)=0 then 'NG' else 'OK' end
 from table_a;

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@postgresql.org so that your
>      message can get through to the mailing list cleanly

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@postgresql.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@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>