Correlated subqueries? Please help!

Hi there,
I want to do something very simple, but in the most efficient way possible.
Let's say I have these two tables:
tblContacts(contact_id, contact_name)
tblPhones(phone_id, contact_id, phone_number, phone_rank)
When querying my contacts, I need to get the phone numbers in tblPhones
whose phone_rank is either = 1 or 2, and I want to display them in two
columns of the resultset, like this:
Name Phone1 Phone2
John Smith 555-5555 666-6666
I've tried correlated subqueries:
select contact_id, contact_name,
(select phone_number from tblPhones where phone_rank=1 and
tblPhones.contact_id = tblContacts.contact_id) as Phone1,
(select phone_number from tblPhones where phone_rank=2 and
tblPhones.contact_id = tblContacts.contact_id) as Phone2
from tblContacts
It works, but seems a bit slow...
I'm sure there are better ways to do this, but I can't figure it out and am
in a hurry...
Any hints would be appreciated!
TIA
Paul Dussault, MCPBest done client side but here's one method.
Works only if a contact can have only one phone per rank.
Untested:
select C.contact_name as [name],
(select phone_number from tblPhones where phone_rank = 1 and contact_ID =
C.contact_ID) as phone1,
(select phone_number from tblPhones where phone_rank = 2 and contact_ID =
C.contact_ID) as phone2
from tblContacts C
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:OCuGNWWYFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I want to do something very simple, but in the most efficient way
> possible.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and
> am in a hurry...
> Any hints would be appreciated!
>
> TIA
>
> Paul Dussault, MCP
>
>|||Paul Dussault wrote:

> Hi there,
> I want to do something very simple, but in the most efficient way possible
.
> Let's say I have these two tables:
> tblContacts(contact_id, contact_name)
> tblPhones(phone_id, contact_id, phone_number, phone_rank)
> When querying my contacts, I need to get the phone numbers in tblPhones
> whose phone_rank is either = 1 or 2, and I want to display them in two
> columns of the resultset, like this:
>
> Name Phone1 Phone2
> John Smith 555-5555 666-6666
>
> I've tried correlated subqueries:
> select contact_id, contact_name,
> (select phone_number from tblPhones where phone_rank=1 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone1,
> (select phone_number from tblPhones where phone_rank=2 and
> tblPhones.contact_id = tblContacts.contact_id) as Phone2
> from tblContacts
> It works, but seems a bit slow...
> I'm sure there are better ways to do this, but I can't figure it out and a
m
> in a hurry...
> Any hints would be appreciated!
Something like this:
select contact_id, contact_name, p1.phone_number, p2.phone_number
from tblContacts c
left outer join tblPhones p1 on
p1.contact_id = c.contact_id and
p1.phone_rank = 1
left outer join tblPhones p2 on
p2.contact_id = c.contact_id and
p2.phone_rank = 2
Regards,
Berend. (-:|||Many Thanks!
I didn't think of the aliases...
Much faster!
Paul Dussault
"Berend de Boer" <berend@.xsol.com> wrote in message
news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> Paul Dussault wrote:
>
> Something like this:
> select contact_id, contact_name, p1.phone_number, p2.phone_number
> from tblContacts c
> left outer join tblPhones p1 on
> p1.contact_id = c.contact_id and
> p1.phone_rank = 1
> left outer join tblPhones p2 on
> p2.contact_id = c.contact_id and
> p2.phone_rank = 2
> Regards,
> Berend. (-:|||Sorry if I sound ignorant, but how do aliases speed up query execution? I
always figured that, if anything, they would slightly slow down execution
since each alias has to be translated to the full path at run time (or at
least I've always assumed that that was what happened).
Chris
"Paul Dussault" wrote:

> Many Thanks!
> I didn't think of the aliases...
> Much faster!
> Paul Dussault
> "Berend de Boer" <berend@.xsol.com> wrote in message
> news:%23v%23tzjWYFHA.3584@.TK2MSFTNGP12.phx.gbl...
>
>|||They don't really. What sped up your query was changing the correlated
subqueries into Left Joins.
Thomas
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:D4D67BF3-2DBB-4A7E-BD28-C84282929D1C@.microsoft.com...
<snip>sql