Обсуждение: multiple lookup per row

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     

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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

"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
phoneFROM 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