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
