-
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é
-
subquery help (reply)
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
-
subquery help (reply)
It worked perfectly! Thanks Al - just what I was looking for.
Appreciate your responding...
André
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|