From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Samuel Gendler <sgendler@ideasculptor.com>
Subject: Re: weird join producing too many rows
Date: Wed, 12 Sep 2012 09:17:00 +0100
User-Agent: KMail/1.9.10
Cc: pgsql-sql@postgresql.org
References: <201209111542.09386.gary.stainburn@ringways.co.uk>
	<CAEV0TzBzrG+Qd=zj2QXJfdgq5XV-xbyP55FE0XJk3_Oxw1OuRA@mail.gmail.com>
In-Reply-To: 
 <CAEV0TzBzrG+Qd=zj2QXJfdgq5XV-xbyP55FE0XJk3_Oxw1OuRA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201209120917.00531.gary.stainburn@ringways.co.uk>

Hi,

Thanks for this.  I did eventually discover the cause being other rows in the 
pieces_requests table that I hadn't thought about.

The short answer to your second part is that I don't know why I did it that 
way. Presumably when I first wrote it there was a reason.

Gary

On Wednesday 12 September 2012 08:24:42 Samuel Gendler wrote:
> I'll admit I don't see any reason why you should get duplicate rows based
> on the data you've provided, but I am wondering why you are using the
> subquery instead of just 'where r.r_id = 5695'
>
> select p.p_id, r.pr_ind
> from pieces p
> join pieces_requests r on p.p_id = r.p_id
> where r.r_id = 5695
>
> Though I'll be the first to admit that that seems to me like it ought to
> return the exact same rows as both your queries.  Are you sure you don't
> have multiple rows in pieces_requests with the same p_id, r_id pairing?
>  Your join must be resulting in multiple rows for each p_id somehow.
>
-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk