Re: [SQL] multiple lookup per row

2001-07-20 Thread Tom Lane
"Josh Berkus" <[EMAIL PROTECTED]> 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 me

Re: [SQL] multiple lookup per row

2001-07-20 Thread Josh Berkus
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 t

Re: [SQL] multiple lookup per row

2001-07-20 Thread Josh Berkus
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'

[SQL] multiple lookup per row

2001-07-20 Thread Gary Stainburn
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.