Results 1 to 8 of 8

Thread: self join query

  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: self join query

    Hi Friends,

    There is a table with two columns containing a long account number and a short account number. like this:
    LongAcctNum ShortAcctNum
    A00146000 A00146
    A00146000 1000146000
    Q78886001 Q78886
    00351250246 351250246

    Each long account number should be having one short account number. But some have two. If I have to pick out such long acct. numbers, what kind of query should I write? Can I use a self-join on this table, something like:
    select LongAcctNum, ShortAcctNum from table1 t1 where t1.LongAcctNum = t1.LongAcctNum and t1.ShortAcctNum != t1.ShortAcctNum.

    I tried this way; it doesn't work. Can my friends here please correct me?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    An easier way is to let DB2 count them for you. Like this:

    with temp1 (longAcctnum,quantity) as (
    select longacctnum,count(*) from table1 group by longacctnum)
    select tp1.longacctnum,t1.shortacctnum from temp1 as tp1 inner join table1 as t1 on (tp1.longacctnum = t1.longacctnum) where tp1.quantity > 1

    HTH

    Andy

  3. #3
    Join Date
    Aug 2006
    Posts
    6
    Hi Andy,

    Thanks for the reply. Can this be done without using temporary tables?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is not a temporary table. It is a common-table-expression. I just named it temp1.

    Andy

  5. #5
    Join Date
    Aug 2006
    Posts
    6
    Oh, ok thanks. I am new to db2. I have earlier worked with Sybase and hadn't come accross common-table-expressions.
    Thank You for your response.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by hnhegde
    select LongAcctNum, ShortAcctNum from table1 t1 where t1.LongAcctNum = t1.LongAcctNum and t1.ShortAcctNum != t1.ShortAcctNum
    This is no self join: you must specify the table twice in the "from" condition for it to be a self join, e.g.:
    Code:
    select t1.LongAcctNum, t1.ShortAcctNum, t2.ShortAcctNum
    from   table1 AS t1 LEFT OUTER JOIN table1 AS t2
           ON t1.LongAcctNum = t2.LongAcctNum and t1.ShortAcctNum != t2.ShortAcctNum
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ARWinner
    with temp1 (longAcctnum,quantity) as ...
    This is a CTE (common table expression). Not available in DB2 prior to version 8.
    The same result can be obtained with
    Code:
    select longAcctnum, count(*) AS quantity
    from   table1
    group by longAcctnum
    having  count(*) > 1
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    On LUW, it has been available atleast from V7 ... On ZOS, CTEs were not introduced till V8

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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