Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: query to select column values which has corresponding entry in another column

    Hi All

    This is my first post here and am trying to learn how to write queries in DB2 mainframes.

    I have a table with the below columns and data

    EVENT

    identifier (FK) ID
    event code(FK) EVNT_CD
    event timestamp EVNT_TS
    type code(FK) TYPE_CD
    event user identifier EVENT_ID
    database insertion timestamp DB_TS

    EVENT_TYPE

    event code EVNT_CD
    type code(FK) TYPE_CD
    event short name EVNT_SN
    event name EVNT_NM
    functional area code(FK) AREA_CD


    For each and every ID in EVENT table, i will have 2 EVNT_CD's, say 01 and 02.
    Now i want to print those ID's for which there is no EVNT_CD "02" with the event name from EVENT_TYPE table and ID, and EVNT_CD

    EVNT_CD will be the common field in both of the tables.

    Thanks a lot in advance!!.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt your description of FKs were incomplete.

    (1) FK for which table?
    (1-1) identifier (FK) ID
    (1-2) functional area code(FK) AREA_CD

    (2) Both of EVENT table and EVENT_TYPE table have "type code(FK) TYPE_CD"

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two examples. Not tested.

    Example 1:
    Code:
    SELECT e1.id
         , e1.evnt_cd
         , tp.event_nm
     FROM  event      AS e1
     INNER JOIN
           event_type AS tp
      ON   tp.evnt_cd = e1.evnt_cd
     WHERE e1.evnt_cd = '01'
       AND NOT EXISTS
          (SELECT 0
            FROM  event AS e2
            WHERE e2.id      = e1.id
              AND e2.evnt_cd = '02'
          )

    Example 2:
    Code:
    SELECT e.id
         , e.evnt_cd
         , t.event_nm
     FROM (SELECT id
                , MIN(evnt_cd) AS evnt_cd
            FROM  event
            GROUP BY
                  id
            HAVING
                  MIN(evnt_cd) = '01'
              AND COUNT(*)     = 1
          )           AS e
     INNER JOIN
           event_type AS t
      ON   t.evnt_cd = e.evnt_cd

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi,Mr tonkuma
    I think function max() also works:
    Code:
    SELECT e.id
         , e.evnt_cd
         , t.event_nm
     FROM (SELECT id
                , MAX(evnt_cd) AS evnt_cd
            FROM  event
            GROUP BY
                  id
            HAVING
                  MAX(evnt_cd) = '01'
          )           AS e
     INNER JOIN
           event_type AS t
      ON   t.evnt_cd = e.evnt_cd

  5. #5
    Join Date
    Sep 2012
    Posts
    3
    Thankyou!. I will try this query and see how it works.

    And tonkuma, for your question, these are not the only tables in the database, i have many other tables in DB.So thats the reason i have the FK.

    Thanks again

  6. #6
    Join Date
    Sep 2012
    Posts
    3
    Hi Again

    I tried the query, but its giving me the ID's and all the events associated with it in EVENT_TYPE. I need to get only 1 entry.
    Looks like the mapping between the EVENT_TYPE and EVENT is not proper.

    Please help me

    Thanks!

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please publish sample data of event table and event_type table.
    And
    What are the primary keys?
    Are there any foreign key constrains between event table and event_type table?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,
    You are right. MAX is better than MIN and COUNT.

    svedanth,
    You wrote "EVNT_CD will be the common field in both of the tables".
    Another common column is type code(TYPE_CD).
    Should we add the column to ON condition?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •