I am trying to create a query that returns all transactions for each person who has a balance over a given amount. I thought HAVING was the answer, but if so, I'm mis-using it. This query returns only transactions exceeding the given amount rather than transactions for people whose balance is over the amount:
SELECT fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,contactnameaddress.postalcode,
transdate,linkednameid,transreference,
transamount,caseid,transcheckno,lastorcompanyname,firstname
FROM trans,ombcase,client,contactnameaddress,linkedname,status
WHERE transistrust <> 1
AND client_fkey = client_pkey
AND case_fkey = case_pkey
AND clientname_fkey = contactnameaddress.contactname_pkey
AND linkedname_fkey = linkedname.contactname_pkey
AND status_fkey = status_pkey
AND lower(statusid) NOT LIKE ('closed%')
AND transcleared <> 1
GROUP BY case_pkey,contactnameaddress.streetaddress,
contactnameaddress.towncityname,
contactnameaddress.stateprovabbrev,
contactnameaddress.postalcode,
transdate,transreference,transamount,
fullname,linkednameid,
contactnameaddress.lastorcompanyname,
contactnameaddress.firstname,caseid,
transcheckno
HAVING sum(transamount)>= 50
Since that returned the wrong set of records, I created another that returns the correct set of people with balances over the given amount. But I can't figure out how to use this to get all the transactions for people returned by this query:
SELECT case_pkey
FROM trans,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND statusopen = 1
AND transistrust <> 1
AND transcleared <> 1
GROUP BY case_pkey
HAVING sum(transamount) >= 50
ORDER BY case_pkey
So how do I get all transactions for each case_pkey? I've read the documentation on WITH clauses (CTEs), but that just left my head spinning.
Chuck Martin
Avondale Software