Re: SELECT Question
От | Alex |
---|---|
Тема | Re: SELECT Question |
Дата | |
Msg-id | 3F52D740.2080207@meerkatsoft.com обсуждение исходный текст |
Ответ на | Re: SELECT Question (Jeffrey Melloy <jmelloy@visualdistortion.org>) |
Список | pgsql-general |
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 > >
В списке pgsql-general по дате отправления: