Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBRfP-00071O-I7 for pgsql-sql@postgresql.org; Tue, 11 Sep 2012 14:42:19 +0000 Received: from hub.ringways.co.uk ([77.86.27.30] helo=mail.ringways.co.uk) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TBRfM-00006P-GV for pgsql-sql@postgresql.org; Tue, 11 Sep 2012 14:42:18 +0000 Received: from localhost ([127.0.0.1] helo=mail.ringways.co.uk) by mail.ringways.co.uk with esmtp (Exim 4.69) (envelope-from ) id 1TBRfG-0006Dx-M6 for pgsql-sql@postgresql.org; Tue, 11 Sep 2012 15:42:12 +0100 Received: from eddie.ringways.co.uk ([10.1.1.115] helo=eddie.ringways.co.uk) by mail.ringways.co.uk with ESMTP id qFg6908X28067; Tue, 11 Sep 2012 15:42:06 +0100 From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: weird join producing too many rows Date: Tue, 11 Sep 2012 15:42:09 +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: <201209111542.09386.gary.stainburn@ringways.co.uk> X-SpamTest-Envelope-From: gary.stainburn@ringways.co.uk X-SpamTest-Info: Profiles 20401 [Mar 31 2011] X-SpamTest-Method: none X-SpamTest-Rate: 0 X-SpamTest-Status: Not detected X-SpamTest-Status-Extended: not_detected X-SpamTest-Version: SMTP-Filter Version 3.0.0 [0285], KAS30/SDK/Release X-Anti-Virus: Kaspersky Mail Gateway, version: 5.6.28/RELEASE, bases: 20110331T110535 #5151353, check: 20120911 clean X-Spam-Score: -51.6 (---------------------------------------------------) X-Spam-Report: Spam detection software, running on the system "ollie.ringways.co.uk", has identified this incoming email as possible spam. The original message has been attached to this so you can view it (if it isn't spam) or label similar future email. If you have any questions, see Gary Stainburn for details. Content preview: I have a pieces table with p_id as primary key. I have a requests table with r_id as primary key. I have a pieces_requests table with (p_id, r_id) as primary key, and an indicator pr_ind reflecting the state of that relationship [...] Content analysis details: (-51.6 points, 15.0 required) pts rule name description ---- ---------------------- -------------------------------------------------- -50 ALL_TRUSTED Passed through trusted hosts only via SMTP -2.6 BAYES_00 BODY: Bayesian spam probability is 0 to 1% [score: 0.0000] 0.0 AWL AWL: From: address is in the auto white-list 1.0 RING_SAFE RING_SAFE X-Pg-Spam-Score: -0.8 (/) X-Archive-Number: 201209/21 X-Sequence-Number: 36823 I have a pieces table with p_id as primary key. I have a requests table with r_id as primary key. I have a pieces_requests table with (p_id, r_id) as primary key, and an indicator pr_ind reflecting the state of that relationship A single select of details from the pieces table based on an entry in the pieces_requests table returns what I expect. users=# select * from pieces_requests where r_id=5695; p_id | r_id | pr_ind ------+------+-------- 5102 | 5695 | 5020 | 5695 | 5065 | 5695 | 5147 | 5695 | 4917 | 5695 | 5165 | 5695 | 4884 | 5695 | 5021 | 5695 | 5121 | 5695 | 5130 | 5695 | 5088 | 5695 | 4900 | 5695 | 4197 | 5695 | 2731 | 5695 | (14 rows) users=# select p_id, p_name from pieces where p_id in (select p_id from pieces_requests where r_id=5695); p_id | p_name ------+--------- 4884 | LSERVB 4900 | ESALES4 5102 | LSALES6 2731 | LSALESE 5147 | ESALES5 5020 | LSALES5 5130 | LSALES3 5021 | WSERV7 4917 | LSALESA 5165 | LSERV8 5088 | LADMIN1 5121 | LSALESL 4197 | WSERV1 5065 | LSALESG (14 rows) users=# However, when I try to include the pr_ind in the result set I get multiple records (at the moment pr_ind is NULL for every record) I've tried both select p.p_id, r.pr_ind from pieces p join pieces_requests r on p.p_id = r.p_id where p.p_id in (select p_id from pieces_requests where r_id=5695) and select p.p_id, r.pr_ind from pieces p, pieces_requests r where p.p_id = r.p_id and p.p_id in (select p_id from pieces_requests where r_id=5695) Both result in the following. Can anyone see why. I think I'm going blind on this one users=# select p.p_id, p_name, r.pr_ind users-# from pieces p, pieces_requests r users-# where p.p_id = r.p_id and users-# p.p_id in (select p_id from pieces_requests where r_id=5695); p_id | p_name | pr_ind ------+---------+-------- 2731 | LSALESE | 2731 | LSALESE | 2731 | LSALESE | 2731 | LSALESE | 4884 | LSERVB | 4900 | ESALES4 | 4900 | ESALES4 | 4917 | LSALESA | 4197 | WSERV1 | 4197 | WSERV1 | 4884 | LSERVB | 5021 | WSERV7 | 5065 | LSALESG | 5065 | LSALESG | 4884 | LSERVB | 5121 | LSALESL | 5088 | LADMIN1 | 5130 | LSALES3 | 5147 | ESALES5 | 5102 | LSALES6 | 5020 | LSALES5 | 5065 | LSALESG | 5147 | ESALES5 | 4917 | LSALESA | 5165 | LSERV8 | 4884 | LSERVB | 5021 | WSERV7 | 5121 | LSALESL | 5130 | LSALES3 | 5088 | LADMIN1 | 4900 | ESALES4 | 4197 | WSERV1 | 2731 | LSALESE | (33 rows) users=# -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk