Aggregate function with Join stop working under certain condition

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

Responses

Browse pgsql-general by date

  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