From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: weird join producing too many rows
Date: Tue, 11 Sep 2012 15:42:09 +0100
Message-ID: <201209111542.09386.gary.stainburn@ringways.co.uk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gary.stainburn@ringways.co.uk
Subject: Re: weird join producing too many rows
In-Reply-To: <201209111542.09386.gary.stainburn@ringways.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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