From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: weird join producing too many rows
Date: Wed, 12 Sep 2012 09:17:00 +0100
Message-ID: <201209120917.00531.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <CAEV0TzBzrG+Qd=zj2QXJfdgq5XV-xbyP55FE0XJk3_Oxw1OuRA@mail.gmail.com>
References: <201209111542.09386.gary.stainburn@ringways.co.uk>
<CAEV0TzBzrG+Qd=zj2QXJfdgq5XV-xbyP55FE0XJk3_Oxw1OuRA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk, sgendler@ideasculptor.com
Subject: Re: weird join producing too many rows
In-Reply-To: <201209120917.00531.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox