pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedweird join producing too many rows
3+ messages / 2 participants
[nested] [flat]
* weird join producing too many rows
@ 2012-09-11 14:42 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Gary Stainburn @ 2012-09-11 14:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: weird join producing too many rows
@ 2012-09-12 07:24 Samuel Gendler <sgendler@ideasculptor.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Samuel Gendler @ 2012-09-12 07:24 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: 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
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: weird join producing too many rows
@ 2012-09-12 08:17 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Samuel Gendler <sgendler@ideasculptor.com>
0 siblings, 0 replies; 3+ messages in thread
From: Gary Stainburn @ 2012-09-12 08:17 UTC (permalink / raw)
To: Samuel Gendler <sgendler@ideasculptor.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-09-12 08:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-11 14:42 weird join producing too many rows Gary Stainburn <gary.stainburn@ringways.co.uk>
2012-09-12 07:24 ` Samuel Gendler <sgendler@ideasculptor.com>
2012-09-12 08:17 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox