pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
multiple 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