I was not familiar with set operations, but studied up a bit and thought I was getting there. Not quite, though. I have two queries that individually return 1) all ombcase records with no statuschange record, and 2) the newest statuschange record for each case that has a statuschange record. But just putting UNION between then doesn't work. Here are my queries:
--First, find all open cases with no statuschange record
SELECT
case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
ombcase
LEFT JOIN
statuschange
ON
statuschange.case_fkey = case_pkey
AND case_pkey <> 0
LEFT JOIN
status
ON status_fkey = status_pkey
WHERE lower(statusid) NOT LIKE ('closed%')
AND statuschange.statuschange_pkey IS NULL
UNION
--Now find the last status change record for each case that has one
SELECT DISTINCT ON (case_fkey)
case_pkey,statuschange_pkey,case_fkey,ombcase.insdatetime,statuschange.insdatetime
FROM
statuschange,ombcase,status
WHERE case_fkey = case_pkey
AND status_fkey = status_pkey
AND LOWER(statusid) NOT LIKE ('closed%')
ORDER BY case_fkey, statuschange.insdatetime DESC
If I run each part separately, I get the expected number of records. When I combine them with UNION, I get "missing FROM-clause entry for table "statuschange"
So I'm very close here, and these two return the exact number of records I'm expecting. So I just need to get them added together. Then I expect I can put the whole thing in a WHERE clause with "AND ombcase.case_pkey IN ([the combined results])"