Re: HAVING query structured wrong

>>>>> "Chuck" == Chuck Martin <clmartin@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)

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)