Can anybody think of a simpler way to run this query?
Table layout:
people
pid (unique), name_last, name_middle, name_first -- with pid::serial
p_phonenumber
pid (not unique), phone_number -- with pid::int
Query:
(select p.pid, name_first, name_last, null::numeric(10,0) as phone_number
from people p
except
select p.pid, name_first, name_last, null::numeric(10,0) as phone_number
from people p, p_phonenumber pn where p.pid=pn.pid
)
union
(select p.pid, name_first, name_last, phone_number
from people p, p_phonenumber pn where p.pid=pn.pid
)
order by pid;
I need a complete set of records (i.e. all of the records in people)
that includes phone number/s for
pid's that have it/them. A pid can have multiple phone numbers.
It seems silly to select the total set, `except` what I need, and then
`union` it with what I need. There must be a simpler way to do this,
but I cannot find it.
I thank you for your assistance.
gh
If it helps, here is the `explain` output:
NOTICE: QUERY PLAN:
Unique (cost=337.77..338.51 rows=7 width=44)
-> Sort (cost=337.77..337.77 rows=74 width=44)
-> Append (cost=0.00..335.47 rows=74 width=44)
-> Seq Scan on people p (cost=0.00..330.10 rows=61
width=28)
SubPlan
-> Materialize (cost=5.37..5.37 rows=13
width=32)
-> Hash Join (cost=1.16..5.37 rows=13
width=32)
-> Seq Scan on people p
(cost=0.00..1.61 rows=61 width=28)
-> Hash (cost=1.13..1.13 rows=13
width=4)
-> Seq Scan on p_phonenumber
pn (cost=0.00..1.13 rows=13 width=4)
-> Hash Join (cost=1.16..5.37 rows=13 width=44)
-> Seq Scan on people p (cost=0.00..1.61 rows=61
width=28)
-> Hash (cost=1.13..1.13 rows=13 width=16)
-> Seq Scan on p_phonenumber pn
(cost=0.00..1.13 rows=13 width=16)
EXPLAIN