Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Unanswered: Joins between tables with different where condition

    Hi,
    Can we join two tables with different where conditions.
    For example there is one table a and another table b.
    Now after selection of data from one table like
    select * from table a where a.col1 = 3 and a.col2 > 0
    Now second selection is like
    select * from table b where b.col1 = 3 and b.col2 > 0

    Then we then join the two tables based on some condition a.col4=b.col4
    and a.col5 = b.col5.

    Can we do the join between two or if there is any otherway to do it.

    Biswa

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you specify the predicates in the WHERE clause, DB2 decides which order to apply them and the join method. Just specify all of the WHERE clause predicates and let DB2 figure it out.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2013
    Posts
    7
    Hi,
    The actual scenario is like this.

    First Query
    Select * from table a
    where a.in = '0' and a.spec > 0 and a.shrt = 'H'

    Second query
    Select * from table b
    where b.in = '0'

    How will we write a join in this actual scenario on join condition of
    a.gr=b.gr and a.id_sfx = b.id_sfx because u can see that
    both the table has one common filter condition as a.in ='0' and b.in='0'

    biswa

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Select a.*, b.*
    from table1 a, table2 b
    where a.in = '0'
    and a.spec > 0
    and a.shrt = 'H'
    and b.in = '0'
    and a.col4 = b.col4
    and a.col5 = b.col5
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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