Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013

    Unanswered: sub QUERY using IN clause - select fields from both queries


    I am trying to use sub query in DB2, the inner query is to filter to what I needed, the parent query is to match to what was filtered.

    The parent query should be able to select any fields from both the inner and the parent query. Can this be possible ?
    The problem I have is that the inner query has only what was filter (one field in the select statement),
    so how can the parent query select something that wasn't selected in the inner query ?

    I haven't had any success in doing this, could someone help me and provide me with some examples.

    I have a sample code that I made up, though the syntax is not correct, however you can get an idea what I am trying to do.

    It needs to use IN and not a join because a join will give more result since the field that connects are not unique.
    IN will give you what is IN from the parent query in the child query and will not duplicate the records.

    Thanks !

    SELECT b2.size, a2.color, IQ.banana 
    FROM apple a2 INNER JOIN orange o2 
    ON a2.1 = o2.1 INNER JOIN pair p2 
    ON p2.1 = a2.1 INNER JOIN banana b2 ON a2.1=b2.1 
    WHERE a2.size = 10 AND p2.color = red AND b2.days = 10 AND a2.color IN 
    (SELECT a.color
    FROM apple a INNER JOIN orange o 
    ON a.1 = o.1 INNER JOIN pair p 
    ON p.1 = a.1 INNER JOIN banana b ON a.1=b.1 
    WHERE a.size = 10 AND p.color = red AND b.days = 10) AS IQ

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    seeing as you have had no responses, thought I would add some tips here. First off your explanation is kind of long running and confusing.
    You cannot select data from a subquery. you can compare multiple fields between your subquery and outer portion.
    It is, typically, faster to use an EXISTS subselect over an IN.

Posting Permissions

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