From: | "Naoko Reeves" <naoko(at)lawlogix(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Aggregate function with Join stop working under certain condition |
Date: | 2009-08-26 15:17:10 |
Message-ID: | 076DC33A3D38CE4BBC64D35DDD9DE70C08F4DC58@mse4be2.mse4.exchange.ms |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-08-26 15:21:42 | Re: Tagged types module and varlena changes |
Previous Message | Alvaro Herrera | 2009-08-26 15:17:06 | Re: ETL software and training |