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;
