Re: weird join producing too many rows

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