Thread: weird join producing too many rows

weird join producing too many rows

From
Gary Stainburn
Date:
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 | LSERVB4900 | ESALES45102 | LSALES62731 | LSALESE5147 | ESALES55020 | LSALES55130 | LSALES35021 |
WSERV74917| LSALESA5165 | LSERV85088 | LADMIN15121 | LSALESL4197 | WSERV15065 | 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 



Re: weird join producing too many rows

From
Samuel Gendler
Date:
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.stainburn@ringways.co.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: weird join producing too many rows

From
Gary Stainburn
Date:
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