Re: weird join producing too many rows

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.

On Tue, Sep 11, 2012 at 7:42 AM, Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:

> 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
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>