Select duplicate records



IBM's flagship relational database management system

Select duplicate records

Postby Kitz » Mon Jun 15, 2015 3:23 pm

Please can any one suggest on my query below. I need only records having more than 1 same customerid.

Select b.customerid, b.accountno
from customer a, account b, address c
where
a.customerid=b.customerid and
b.accountno=c.accountno and
b.status='active'
group by b.customerid, b,accountno
having count(customerid) > 1;


For some reasons, I am getting single records along with duplicate records. I am only looking for duplicate records.

Eg; When I execute my query, I am geting the below records.
customerid accountno
A111 12345
A111 23456
B123 65432
C222 10101
C222 11122

But I am looking for only the below records:
A111 12345
A111 23456
C222 10101
C222 11122


Please help.
Kitz
 
Posts: 47
Joined: Thu Mar 17, 2011 3:46 am
Has thanked: 0 time
Been thanked: 1 time

Re: Select duplicate records

Postby alexm » Tue Jun 16, 2015 11:39 pm

Hi Kitz,

would you mind passing us your (relevant) table definitions for customer, account and address, and provide some data for these three tables?
BTW, your 'having' clause refers to a unqualified column 'customerid', but it looks like customerid is a member of both customer and account tables.

Thank you
alexm
User avatar
alexm
 
Posts: 35
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post