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
