Re: Aggregate function with Join stop working under certain condition

On Wed, Aug 26, 2009 at 11:17:10AM -0400, Naoko Reeves wrote:
> I am joining 4 tables (doc, bpt, con, emp) - emp and doc relationship is
> one to many. I want to only one doc record per emp as condition shown
> below:

[...]

> However, I wan to add one more doc column but as soon as I add one, it
> try to return all unique doc records. Could you tell me what am I doing
> wrong here please?

Descriptions of the problem are normally easier to understand than code;
but I *guess* what you want to do is to get the subject of the last
document created by each person and when it was created. If that's the
case then DISTINCT ON is normally the easiest way. Maybe something
like:

SELECT b.bpt_key, e.emp_full_name, c.con_full_name,
d.doc_date_created, d.doc_subject
FROM bpt b, emp e
LEFT JOIN con c ON e.emp_con_key = c.con_key
LEFT JOIN (
SELECT DISTINCT ON (doc_emp_key) doc_emp_key,
doc_date_created, doc_subject
FROM doc
ORDER BY doc_emp_key, doc_date_created DESC) d
ON e.emp_key = d.doc_emp_key
WHERE b.bpt_emp_key = e.emp_key
AND b.bpt_com_key = 22
AND b.bpt_status <> -1;

--
Sam http://samason.me.uk/