Thread: weird join producing too many rows

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 

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