Thread: Aggregate function with Join stop working under certain condition
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:
The query below successfully returns desired result (returns 4 records):
Select bpt_key, emp_full_name, con_full_name, max(doc_date_created) as doc_date_created
from bpt
LEFT OUTER JOIN doc ON bpt_emp_key = doc_emp_key
INNER JOIN emp on bpt_emp_key = emp_key
LEFT OUTER JOIN con ON emp_con_key = con_key
WHERE bpt_com_key = 22 and bpt_status<>-1
GROUP BY bpt_key, emp_full_name, con_full_name
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?
As soon as I add one more column, it returns 6 records:
Select bpt_key, emp_full_name, con_full_name, max(doc_date_created) as doc_date_created, doc_subject
from bpt
LEFT OUTER JOIN doc ON bpt_emp_key = doc_emp_key
INNER JOIN emp on bpt_emp_key = emp_key
LEFT OUTER JOIN con ON emp_con_key = con_key
WHERE bpt_com_key = 22 and bpt_status<>-1
GROUP BY bpt_key, emp_full_name, con_full_name, doc_subject
Kindest Regard,
Naoko
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/
The solution provide by Sam is unbelievably fast and works 100%
accurately. Thank you very much.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason
Sent: Wednesday, August 26, 2009 10:40 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general