pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmultiple lookup per row
4+ messages / 3 participants
[nested] [flat]
* multiple lookup per row
@ 2001-07-20 09:39 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2001-07-20 09:39 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I've got a table 'phones' which has an indexed key 'pid' of type int4, and a
phone number of type varchar(12).
I've then got a table 'members' which as an index key 'mid' of type int4.
Now, obviously, if each member only had one phone number, I could simply pull
it in using a join. My problem is that each member has (potentially) a voice
line 'mphone', a fax line 'mfax', and a mobile (that's a cellphone to you lot
over the pond) 'mmobile'.
How would I embelish a 'select * from members........' so that it included
the three phone numbers if they exist?
The only solution I can think of is to write a plpgsql function to do the
lookup and call that three times as part of the select, something like
select *, getphone(mphone) as phone, getphone(mfax) as fax.....
but a purely SQL solution would be nicer.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple lookup per row
@ 2001-07-20 14:41 Josh Berkus <josh@agliodbs.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2001-07-20 14:41 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
Gary,
First: Go out and buy "SQL for Smarties". Now. Read it.
However, I'll give you this one as a freebie:
> I've got a table 'phones' which has an indexed key 'pid' of type
> int4, and a
> phone number of type varchar(12).
>
> I've then got a table 'members' which as an index key 'mid' of type
> int4.
SELECT members.mid, members.name, members.address, hp.phone AS
home_phone, wp.phone AS work_phone, cp.phone as cell_phone
FROM members LEFT OUTER JOIN
(SELECT mid, phone FROM phones WHERE ptype = 'home') hp
ON members.mid = hp.mid
LEFT OUTER JOIN
(SELECT mid, phone FROM phones WHERE ptype = 'work') wp
ON members.mid = wp.mid
LEFT OUTER JOIN
(SELECT mid, phone FROM phones WHERE ptype = 'cell') cp
ON members.mid = cp.mid
ORDER BY members.name;
You're experiencing the usual problem encountered by procedural
programmers when they first start on SQL. SQL is a declarative
language, and requires a different knid of thinking than procedural
languages. Thus the use of table aliasing and subselects above.
Have fun!
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple lookup per row
@ 2001-07-20 15:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-07-20 15:47 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> ... LEFT OUTER JOIN ...
Another way is correlated subselects in the output list:
SELECT mid, name, address,
(SELECT phone FROM phones
WHERE members.mid = phones.mid and ptype = 'home') AS home_phone,
(SELECT phone FROM phones
WHERE members.mid = phones.mid and ptype = 'work') AS work_phone,
(SELECT phone FROM phones
WHERE members.mid = phones.mid and ptype = 'cell') AS cell_phone
FROM members;
With either of these approaches, you'll get NULLs for cases where the
member has no phone number of the given type. However, what you ought
to think about is what happens if the member has more than one phone
number of a single type. With the outer join you will get multiple
output rows for that member, which is likely not what you want. With
my way, you'd get an execution error, which is definitely not what you
want... but you could patch it by including LIMIT 1 in the sub-SELECTs,
and perhaps also an ORDER BY to determine *which* phone number is the
single one shown.
BTW, I second Josh' recommendation of "SQL for Smarties".
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: multiple lookup per row
@ 2001-07-20 15:58 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2001-07-20 15:58 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary,
> I had the same problem the first time I went from writing sequential
> batch
> mainframe apps to event-driven interactive windows apps. Different
> mindset
> completely.
I grapple with the procedural <--> declarative switchover all the time.
In a way, procedural is easier, since set theory does not come
intuitively to any humans I know. In my biggest pgsql app there's quite
a number of things I did with LOOPs that could have been done with
properly structured queries.
> BTW, which would run fastest, or would there be much difference? (I
> would
> have thought not much as the plpgsql function still has to do the
> same select
> as your sub-selects are.)
Yes, but plpgsql does the selects one at a time, evaluates the response,
and proceeds on to the next. The declarative statement does them all in
batches, as the SQL engine is designed to do. Plus my way and Tom's way
can be built as views, allowing further optimization. Not to mention
that pure SQL will always be faster than even a trusted language.
In other words, my and Tom's queries should run about 50 times as fast
as the procedural method for large tables with indexes.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-07-20 15:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-20 09:39 multiple lookup per row Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-07-20 14:41 ` Josh Berkus <josh@agliodbs.com>
2001-07-20 15:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-20 15:58 Re: multiple lookup per row Josh Berkus <josh@agliodbs.com>
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