IBM Mainframe Forum

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.

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