Self Join Problem

[CDM]

I'm trying to create a report based on an SQL statement that has me a little
stumped. Let me start with the fields in my table:
Customer_Id
Group_Id
BalanceDue
PastDue

I want to create an SQL statement that selects a list of customers with a
past due balance. However, some customers have multiple accounts and I use
the Group_Id field to "group" those related accounts. If one account has a
past due balance I want all the related accounts to show up on the list
whether or not they are past due. That's the part I'm having trouble with.
Any ideas out there?

 

[MGFoster]

I'm trying to create a report based on an SQL statement that has me a little
stumped. Let me start with the fields in my table:
Customer_Id
Group_Id
BalanceDue
PastDue

I want to create an SQL statement that selects a list of customers with a
past due balance. However, some customers have multiple accounts and I use
the Group_Id field to "group" those related accounts. If one account has a
past due balance I want all the related accounts to show up on the list
whether or not they are past due. That's the part I'm having trouble with.
Any ideas out there?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably this:

SELECT customer_id, group_id, BalanceDue, PastDue
FROM table_name As T1
WHERE group_id IN (SELECT group_id FROM table_name
WHERE PastDue IS NOT NULL
AND customer_id = T1.customer_id)
ORDER BY customer_id, group_id, BalanceDue DESC

table_name is the same table in the sub-query and the main query.


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmKVWIechKqOuFEgEQLmiACg4whl1FITZX0sUPUydsxVMa+PZVYAnigZ
/WZ/1Cnp96hwIzguxClqQQbE
=NsFz
-----END PGP SIGNATURE-----