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
