From: | Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> |
---|---|
To: | Alex <alex(at)meerkatsoft(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT Question |
Date: | 2003-08-31 18:28:47 |
Message-ID: | F628E326-DBE0-11D7-92C3-000393C78AC0@visualdistortion.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2003-08-31 18:38:24 | Re: About GPL and proprietary software |
Previous Message | Stephan Szabo | 2003-08-31 18:21:20 | Re: SELECT Question |