Aggregate function with Join stop working under certain condition
От | Naoko Reeves |
---|---|
Тема | Aggregate function with Join stop working under certain condition |
Дата | |
Msg-id | 076DC33A3D38CE4BBC64D35DDD9DE70C08F4DC58@mse4be2.mse4.exchange.ms обсуждение исходный текст |
Ответы |
Re: Aggregate function with Join stop working under certain condition
|
Список | 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
В списке pgsql-general по дате отправления: