Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Joining with subquery

    Hi. I'm new to DB2, and inexperienced with SQL in general, so excuse me if this task is somewhat common.

    I have to write two sub-selects coming from the same table that are INTERSECT'ed. I need to join this intersection with another table. The selects look like this (replaced column names for clarity):


    Code:
    SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
    WHERE EVENT_NAME = 'A' 
    
    INTERSECT
    
    SELECT PERSON_KEY, EVENT_DATE  FROM TABLE1
    WHERE EVENT_NAME = 'B'
    There is a table (TABLE2) which contains the PERSON_KEY as well as FIRSTNAME and LASTNAME. I wish to select the PERSON_KEY, FIRSTNAME and LASTNAME of all the people from the intersection.

    Thanks,
    Concubicycle

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by concubicycle View Post
    Hi. I'm new to DB2, and inexperienced with SQL in general, so excuse me if this task is somewhat common.

    I have to write two sub-selects coming from the same table that are INTERSECT'ed. I need to join this intersection with another table. The selects look like this (replaced column names for clarity):


    Code:
    SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
    WHERE EVENT_NAME = 'A' 
    
    INTERSECT
    
    SELECT PERSON_KEY, EVENT_DATE  FROM TABLE1
    WHERE EVENT_NAME = 'B'
    There is a table (TABLE2) which contains the PERSON_KEY as well as FIRSTNAME and LASTNAME. I wish to select the PERSON_KEY, FIRSTNAME and LASTNAME of all the people from the intersection.

    Thanks,
    Concubicycle
    Code:
    SELECT A.PERSON_KEY, B.FIRSTNAME, B.LASTNAME
    FROM (
        SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
        WHERE EVENT_NAME = 'A' 
        INTERSECT
        SELECT PERSON_KEY, EVENT_DATE  FROM TABLE1
        WHERE EVENT_NAME = 'B'
    ) AS A
    JOIN TABLE2 AS B
        ON A.PERSON_KEY = B.PERSON_KEY
    In my experience INTERSECT is pretty slow, som you might want to try for example:

    Code:
        SELECT DISTINCT PERSON_KEY, EVENT_DATE FROM TABLE1 AS A
        WHERE EVENT_NAME = 'A'
              AND EXISTS (
                      SELECT PERSON_KEY, EVENT_DATE  FROM TABLE1 AS B
                      WHERE EVENT_NAME = 'B'
                            AND (A.PERSON_KEY, A.EVENT_DATE)
                                 = (B.PERSON_KEY, B.EVENT_DATE)
              )
    --
    Lennart

  3. #3
    Join Date
    May 2012
    Posts
    2
    Thanks so much =)!

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A common table expression (a "with" expression) could be more readable (but otherwise fully equivalent to lelle12's query) when you want to further process an intermediate result table:
    Code:
    WITH a AS (
        SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
        WHERE EVENT_NAME = 'A' 
        INTERSECT
        SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
        WHERE EVENT_NAME = 'B'
    )
    SELECT A.PERSON_KEY, B.FIRSTNAME, B.LASTNAME
    FROM A INNER JOIN TABLE2 AS B
        ON A.PERSON_KEY = B.PERSON_KEY
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Those queries would produce expected results.

    Note:
    If (A.PERSON_KEY , A.EVENT_NAME) was unique,
    COUNT( DISTINCT A.EVENT_NAME ) could be replaced by COUNT(*)

    Example 1:
    Code:
    SELECT A.PERSON_KEY
         , MAX(B.FIRSTNAME) AS FIRSTNAME
         , MAX(B.LASTNAME ) AS LASTNAME
     FROM  TABLE1 AS A
     INNER JOIN
           TABLE2 AS B
      ON   A.PERSON_KEY = B.PERSON_KEY
     WHERE A.EVENT_NAME IN ('A' , 'B') 
     GROUP BY
           A.PERSON_KEY
     HAVING
           COUNT( DISTINCT A.EVENT_NAME ) > 1
    ;
    Example 2:
    Code:
    SELECT A.PERSON_KEY
         , B.FIRSTNAME
         , B.LASTNAME
     FROM (SELECT PERSON_KEY
            FROM  TABLE1
            WHERE EVENT_NAME IN ('A' , 'B') 
            GROUP BY
                  PERSON_KEY
            HAVING
                  COUNT( DISTINCT EVENT_NAME ) > 1
          ) A
     INNER JOIN
           TABLE2 AS B
      ON   A.PERSON_KEY = B.PERSON_KEY
    ;
    Last edited by tonkuma; 05-26-12 at 12:08.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    hi tonkunma,
    Why do you discard the column EVENT_DATE?
    Maybe the sql should be like this :

    Code:
    SELECT A.PERSON_KEY
         , B.FIRSTNAME
         , B.LASTNAME
     FROM (SELECT PERSON_KEY,EVENT_DATE
            FROM  TABLE1
            WHERE EVENT_NAME IN ('A' , 'B') 
            GROUP BY
                  PERSON_KEY,EVENT_DATE
            HAVING
                  COUNT( DISTINCT EVENT_NAME ) > 1
          ) A
     INNER JOIN
           TABLE2 AS B
      ON   A.PERSON_KEY = B.PERSON_KEY
    ;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,

    You wrote good point!

    Because OP's results inclueded EVENT_DATE, like...
    Code:
    SELECT PERSON_KEY, EVENT_DATE FROM TABLE1
    WHERE EVENT_NAME = 'A' 
    
    INTERSECT
    
    SELECT PERSON_KEY, EVENT_DATE  FROM TABLE1
    WHERE EVENT_NAME = 'B'
    I'll conside more later, because I drunk now.
    Last edited by tonkuma; 05-27-12 at 13:43.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,

    You are correct!

    It may be better to include EVENT_DATE in final result, too.
    Although OP doesn't request EVENT_DATE,
    he/she might want to know which EVENT_DATE are having event 'A' and 'B'.

    Code:
    SELECT A.PERSON_KEY
         , A.EVENT_DATE
         , B.FIRSTNAME
         , B.LASTNAME
     FROM (SELECT PERSON_KEY , EVENT_DATE
            FROM  TABLE1
            WHERE EVENT_NAME IN ('A' , 'B') 
            GROUP BY
                  PERSON_KEY , EVENT_DATE
            HAVING
                  COUNT( DISTINCT EVENT_NAME ) > 1
          ) A
     INNER JOIN
           TABLE2 AS B
      ON   A.PERSON_KEY = B.PERSON_KEY
    ;
    If EVENT_DATE was not neccesary,
    it might be better to specify DISTINCT.
    Code:
    SELECT DISTINCT
           A.PERSON_KEY
         , B.FIRSTNAME
         , B.LASTNAME
     FROM (SELECT PERSON_KEY , EVENT_DATE
            FROM  TABLE1
            WHERE EVENT_NAME IN ('A' , 'B') 
            GROUP BY
                  PERSON_KEY , EVENT_DATE
            HAVING
                  COUNT( DISTINCT EVENT_NAME ) > 1
          ) A
     INNER JOIN
           TABLE2 AS B
      ON   A.PERSON_KEY = B.PERSON_KEY
    ;
    Last edited by tonkuma; 05-28-12 at 06:22. Reason: Add a reason to include EVENT_DATE. Add another example specified DISTINCT.

Posting Permissions

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