On Mon, Feb 18, 2019 at 12:37 PM Andrew Gierth <and...@tao11.riddles.org.uk> wrote:
> >>>>> "Chuck" == Chuck Martin <clmar...@theombudsman.com> writes: > > Chuck> I am trying to create a query that returns all transactions for > Chuck> each person who has a balance over a given amount. I thought > Chuck> HAVING was the answer, but if so, I'm mis-using it. This query > Chuck> returns only transactions exceeding the given amount rather than > Chuck> transactions for people whose balance is over the amount: > [snip] > Chuck> Since that returned the wrong set of records, I created another > Chuck> that returns the correct set of people with balances over the > Chuck> given amount. But I can't figure out how to use this to get all > Chuck> the transactions for people returned by this query: > > Chuck> SELECT case_pkey > Chuck> FROM trans,ombcase,status > Chuck> WHERE case_fkey = case_pkey > Chuck> AND status_fkey = status_pkey > Chuck> AND statusopen = 1 > Chuck> AND transistrust <> 1 > Chuck> AND transcleared <> 1 > Chuck> GROUP BY case_pkey > Chuck> HAVING sum(transamount) >= 50 > > Chuck> ORDER BY case_pkey > > Chuck> So how do I get all transactions for each case_pkey? > > You can join the result of any subquery as if it were a table, either > with or without using a CTE: > > SELECT ... > FROM (select case_pkey from ... having ...) AS cases, > trans > WHERE trans.case_fkey = cases.case_pkey; > > (incidentally, please qualify all the column references in your query > with a table name or alias, otherwise people reading your code have no > idea which column is supposed to be in which table) Sorry. That was sloppy. But working with this idea, I got the query working, so I appreciate the pointer. For anyone wanting to see how I did so, here is the working query (replacing the variable for the minimum balance to include with "50"): -- Find transactions for client invoices using subquery to find client total SELECT contactnameaddress.fullname,contactnameaddress.streetaddress,contactnameaddress.towncityname, contactnameaddress.stateprovabbrev,contactnameaddress.postalcode, trans.transdate,linkedname.linkednameid,trans.transreference, trans.transamount,ombcase.caseid,trans.transcheckno,contactnameaddress.lastorcompanyname,contactnameaddress.firstname FROM trans,ombcase,client,contactnameaddress,linkedname,status , (SELECT case_pkey FROM ombcase,trans , status WHERE trans.case_fkey = ombcase.case_pkey AND ombcase.status_fkey = status_pkey AND status.statusopen = 1 AND trans.transistrust <> 1 AND trans.transcleared <> 1 GROUP BY ombcase.case_pkey HAVING sum(trans.transamount) >= 50) AS cases WHERE trans.case_fkey = cases.case_pkey AND trans.transistrust <> 1 AND ombcase.client_fkey = client.client_pkey AND client.clientname_fkey = contactnameaddress.contactname_pkey AND trans.linkedname_fkey = linkedname.contactname_pkey AND ombcase.status_fkey = status.status_pkey AND status.statusopen = 1 AND trans.transcleared <> 1 AND trans.Case_fkey = ombcase.case_pkey GROUP BY ombcase.case_pkey,contactnameaddress.streetaddress, contactnameaddress.towncityname, contactnameaddress.stateprovabbrev, contactnameaddress.postalcode, trans.transdate,trans.transreference,trans.transamount, contactnameaddress.fullname,linkedname.linkednameid, contactnameaddress.lastorcompanyname, contactnameaddress.firstname,ombcase.caseid, trans.transcheckno I'll try to learn the other methods using your examples. Again, I appreciate the help. or with a CTE, > > WITH cases AS (select ... from ... having ...) > SELECT ... > FROM cases, trans > WHERE trans.case_fkey = cases.case_pkey; > > There's also a third method with window functions instead of GROUP BY, > which is to do something like > > SELECT ... > FROM (select ..., > sum(transamount) over (partition by case_pkey) as total_amt > from ...) s > WHERE total_amt > 50; > > -- > Andrew (irc:RhodiumToad) > >
