subquery help

i have a table which i`m having difficulty setting up a subquery on.

cmpcode code grpcode
------------ --------- ------------
CORP 96020 01ADMIN
HON 96020 01ADMIN
LON 96020 04FOREIGN
LON 96020 01DIRECT
LON 96020 03ELLIOTT
LON 96020 02ACTIVE
NEW 96020 02INACTIVE
NEW 96020 01ADMIN
NEW 96020 03HOLECEK
SIN 96020 01ADMIN

what i would like to do is pull in only `codes` with a grpcode in (02active, 01direct). in the example above, i would only want the `lon` cmpcode to appear, since it`s both 01direct and 02active. since the grpcodes are on different lines, i`m not sure how to accomplish this. also, my key is cmpcode, code - not just code. here`s how i`ve been attempting to do it:

select
cmpcode,
code,
grpcode
from oas_grplist
where
elmlevel = 5 and
grpcode = `02ACTIVE` and
code in(select code from coda..oas_grplist where grpcode = `01direct`).

the problem with this is the subquery join is only based on joining code, and cmpcode needs to be included in the join.

any ideas?

thanks in advance, André

On 6/22/98 5:53:06 PM, André Guerrero wrote:
> i have a table which i`m having difficulty setting up a subquery
> on.

cmpcode code grpcode
------------ ---------
> ------------
CORP 96020 01ADMIN
HON 96020
> 01ADMIN
LON 96020 04FOREIGN
LON 96020
> 01DIRECT
LON 96020 03ELLIOTT
LON 96020
> 02ACTIVE
NEW 96020 02INACTIVE
NEW 96020
> 01ADMIN
NEW 96020 03HOLECEK
SIN 96020
> 01ADMIN

what i would like to do is pull in only `codes` with a
> grpcode in (02active, 01direct). in the example above, i would only want
> the `lon` cmpcode to appear, since it`s both 01direct and 02active. since
> the grpcodes are on different lines, i`m not sure how to accomplish this.
> also, my key is cmpcode, code - not just code. here`s how i`ve been
> attempting to do it:

select
cmpcode,
code,
grpcode
from
> oas_grplist
where
elmlevel = 5 and
grpcode = `02ACTIVE` and
code
> in(select code from coda..oas_grplist where grpcode = `01direct`).

the
> problem with this is the subquery join is only based on joining code, and
> cmpcode needs to be included in the join.

any ideas?

thanks in
> advance, André

Try the following:

select
cmpcode,
code,
grpcode
from
oas_grplist OG1
where
elmlevel = 5 and
grpcode = `02ACTIVE` and
code in ( select code
from coda..oas_grplist OG2
where OG2.grpcode = `01direct` and
OG2.code = OG1.code and
OG2.cmpcode = OG1.cmpcode ).

Al

It worked perfectly! Thanks Al - just what I was looking for.

Appreciate your responding...

André