I have a couple of tables that look like this (not excactly but close
enough):
[Contact]
id int
fname varchar(50)
lname varchar(50)
[ContactPhoneNumber]
id int
number varchar(15)
ext varchar(6)
contact_id int
priority int (indicates primary, secondary... numbers)
type int (indicates type of number: fax, cell, land line)
I'm looking for a more optimized method of displaying this information
in this format:
fname, primary business phone
Using a derived column like this works, but seems to be slow with many
records, despite tuning indexes:
SELECT c.fname AS [First Name],
( SELECT TOP 1
number
FROM ContactPhoneNumber cpn
WHERE cpn.type = 1
AND cpn.contact_id = c.id
ORDER BY cpn.priority) AS Number
FROM Contact c
I can get the same results using a join, and it's a lot faster. But I'm
not sure how to select only the primary phone number this way...
basically the first phone number whose priority is either NULL or 1.
Any suggestions?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!