Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Unanswered:

    Is there a way to write multi-column subqueries in DB2? I tried following

    select * from table1 where (field1,field2) not in (select field1,field2 from table2 )

    thanks

  2. #2
    Join Date
    May 2006
    Posts
    16
    Try

    select * from table1 a
    where not exists ( Select 1 from table2 b
    where b.field1 = a.field1
    and b.field2 = a.field2 )

    Saludos, ARLF.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Both queries are perfectly fine. What's the problem you have with the first, non-correlated subquery?

    Code:
    $ db2 "select * from t where (a, b) not in ( select c1, c2 from x )"
    
    A           B
    ----------- -----------
    
      0 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select * from table1 where (field1,field2) not in (select field1,field2 from table2 )
    You can do that in DB2 for LUW. DB2 for z/OS also support that syntax.

    For more general syntax, you can see in
    "DB2 SQL Reference" ---> "Chapter 2. Language elements" ---> "Predicates" ---> "IN predicate" and "Quantified predicate".

    You can see DB2 9 for LUW PDF manuals here.
    http://www-01.ibm.com/support/docvie...id=swg27009552
    or
    http://www-01.ibm.com/support/docvie...id=swg27009552
    DB2 9 for Linux, UNIX and Windows manuals

    Manual

    Abstract
    This page contains English Portable Document Format (PDF) manuals for DB2® 9 for Linux®, UNIX® and Windows® products, and additional components such as DB2 Connect™ and WebSphere® Federation Server.

Posting Permissions

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