Re: weird join producing too many rows - Mailing list pgsql-sql
| From | Samuel Gendler |
|---|---|
| Subject | Re: weird join producing too many rows |
| Date | |
| Msg-id | CAEV0TzBzrG+Qd=zj2QXJfdgq5XV-xbyP55FE0XJk3_Oxw1OuRA@mail.gmail.com Whole thread Raw |
| In response to | weird join producing too many rows (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
| Responses |
Re: weird join producing too many rows
|
| List | pgsql-sql |
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
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