Results 1 to 3 of 3

Thread: subquery help

  1. #1
    André Guerrero Guest

    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é

  2. #2
    Al Guest

    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

  3. #3
    André Guerrero Guest

    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
  •