Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    16

    Unanswered: How to compare two records based on two different ids in a table

    Hi

    How can I compare two rows in a table and each row is based on different row_id in DB2

    like

    1st row: select fst_nam,last_name,addr from contact where row_id='1-urfye'
    2 nd row: select fst_nam,last_name,addr from contact where row_id='1-iyrwh'


    I want to compare each column of both the above queries and have to get the output as true if all the columns matched otherwise false.

    Please help me..

    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can do exactly what you describe. We take the CONTACT table and find the row identified by the first ID. Then we combine this with the row having the second ID. We need a join for that. Since the other row is in the same table, we just have to join with table CONTACT. (That results in a so-called self-join.) Then we apply your filter criteria, i.e. the values of all other columns must match.
    Code:
    SELECT 'true', t1.row_id, t2.row_id
    FROM   contact AS t1, contact AS t2
    WHERE  t1.row_id = '1-urfye' AND
           t2.row_id = '1-iyrwh' AND
           t1.fst_nam = t2.fst_name AND
           t1.last_name = t2.last_name AND
           t1.addr = t2.addr
    Note: you need to enhance the predicates if you may have NULLs in any of the column. NULL is not a value and, therefore, cannot be compared to any other value and NULL = 'abc' is "unknown", which evaluates to "false" in a WHERE clause of a query.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2009
    Posts
    16
    Thank you Stolze..

Posting Permissions

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