Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jehZS-0007W7-Jf for pgsql-sql@arkaria.postgresql.org; Fri, 29 May 2020 16:09:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jehZR-0008Js-IX for pgsql-sql@arkaria.postgresql.org; Fri, 29 May 2020 16:09:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jehZR-0008Jk-Cb for pgsql-sql@lists.postgresql.org; Fri, 29 May 2020 16:09:21 +0000 Received: from hub.ringways.co.uk ([88.211.105.30] helo=ringways.co.uk) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jehZP-0007qK-FG for pgsql-sql@postgresql.org; Fri, 29 May 2020 16:09:21 +0000 Received: from eddie.ringways.co.uk ([10.1.1.115]) by ringways.co.uk with esmtps (TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.92.3) (envelope-from ) id 1jehZN-0002US-LC for pgsql-sql@postgresql.org; Fri, 29 May 2020 17:09:17 +0100 From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: left outer join with count Date: Fri, 29 May 2020 17:09:16 +0100 User-Agent: KMail/1.9.10 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <202005291709.16647.gary.stainburn@ringways.co.uk> X-Spam-Score: -49.8 (-------------------------------------------------) X-Spam-Report: Spam detection software, running on the system "ollie2.ringways.co.uk", has NOT identified this incoming email as spam. The original message has been attached to this so you can view it or label similar future email. If you have any questions, see Gary Stainburn for details. Content preview: I have the following select: select sj.*, sr.*, wd.doc_count from service_jobs sj left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id left outer join (select sj_id, count(sj_id) as doc_count from work_documents wd group by sj_id) wd on wd.wd_sj_id = sj.sj_id; [...] Content analysis details: (-49.8 points, 15.0 required) pts rule name description ---- ---------------------- -------------------------------------------------- -50 ALL_TRUSTED Passed through trusted hosts only via SMTP -1.9 BAYES_00 BODY: Bayes spam probability is 0 to 1% [score: 0.0021] 0.1 SCORE_RCPTS Adding score for each recipient 1.0 MISSING_FROM Missing From: header 1.0 RING_SAFE No description available. List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk I have the following select: select sj.*, sr.*, wd.doc_count from service_jobs sj left outer join service_receptions sr on sr.sr_id = sj.sj_sr_id left outer join (select sj_id, count(sj_id) as doc_count from work_documents wd group by sj_id) wd on wd.wd_sj_id = sj.sj_id; While the select works, I can't help thinking that the last join is expensive. Is there a cleaner (quicker) method of doing this? Gary