Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    15

    Unanswered: INTERSECT only when the 2nd query has results

    Hi I have 2 queries and I want to make them into 1 to achieve the following result:
    Code:
    ________________________
    Case 1:
    Query1      Query2
    ---------   -------
    1               1
    
    Result
    --------
    1
    ________________________
    Case 2:
    Query1      Query2
    ---------   -------
    1               2
    
    Result
    --------
    ________________________
    Case 3:
    Query1      Query2
    ---------   -------
    1               
    
    Result
    --------
    1
    _________________________
    i've tried

    Query1
    INTERSECT
    Query2
    only works in case 1 and 2. how do I make it to work for query 3?

    In sql server i might try

    IF EXISTS(QUERY2)
    THEN
    QUERY1
    INTERSECT
    QUERY2
    ELSE
    QUERY1
    END

    But the above is not working for me in db2.

    Thanks in advance!

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    maybe I am oversimplifying, but isn't this what a left outer join is for? Hard to tell not seeing your SQL and all.
    Dave

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

    Code:
    Query1
    INTERSECT
    (
    Query2
    UNION ALL
    SELECT *
      FROM (Query1) q
     WHERE NOT EXISTS(Query2)
    )

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For example:
    (Used "SELECT * FROM Query1" instead of simple "Query1", because of using common table expressions.)

    Query2 exists
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Query1 AS (
    SELECT empno , firstnme , workdept , sex
      FROM employee
     WHERE workdept = 'D11'
    )
    ,Query2 AS (
    SELECT empno , firstnme , workdept , sex
      FROM employee
     WHERE sex = 'F'
    )
    SELECT * FROM Query1
    INTERSECT
    (
    SELECT * FROM Query2
    UNION ALL
    SELECT * FROM Query1
     WHERE NOT EXISTS
           (SELECT 0 FROM Query2)
    )
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     WORKDEPT SEX
    ------ ------------ -------- ---
    000160 ELIZABETH    D11      F  
    000180 MARILYN      D11      F  
    000220 JENNIFER     D11      F  
    
      3 record(s) selected.
    Query2 not exists
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     Query1 AS (
    SELECT empno , firstnme , workdept , sex
      FROM employee
     WHERE workdept = 'D11'
    )
    ,Query2 AS (
    SELECT empno , firstnme , workdept , sex
      FROM employee
     WHERE sex = 'X'
    )
    SELECT * FROM Query1
    INTERSECT
    (
    SELECT * FROM Query2
    UNION ALL
    SELECT * FROM Query1
     WHERE NOT EXISTS
           (SELECT 0 FROM Query2)
    )
    ;
    ------------------------------------------------------------------------------
    
    EMPNO  FIRSTNME     WORKDEPT SEX
    ------ ------------ -------- ---
    000060 IRVING       D11      M  
    000150 BRUCE        D11      M  
    000160 ELIZABETH    D11      F  
    000170 MASATOSHI    D11      M  
    000180 MARILYN      D11      F  
    000190 JAMES        D11      M  
    000200 DAVID        D11      M  
    000210 WILLIAM      D11      M  
    000220 JENNIFER     D11      F  
    
      9 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (Used "SELECT * FROM Query1" instead of simple "Query1", because of using common table expressions.)
    By changing the example as following,
    it will be more consistent with the general query form which I presented.

    Code:
    SELECT * FROM Query1
    INTERSECT
    (
    SELECT * FROM Query2
    UNION ALL
    SELECT *
      FROM (SELECT * FROM Query1) q
     WHERE NOT EXISTS
           (SELECT * FROM Query2)
    )

  6. #6
    Join Date
    Dec 2007
    Posts
    15
    Thanks, I think this should work.

    Quote Originally Posted by tonkuma View Post
    Maybe.....

    Code:
    Query1
    INTERSECT
    (
    Query2
    UNION ALL
    SELECT *
      FROM (Query1) q
     WHERE NOT EXISTS(Query2)
    )

Posting Permissions

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