sql - How to join a one-to-many relationship to ensure that only 1 row from the left comes back? -

Sorry about the obscure title, but what I'm trying to do here

My Has the following two tables:

 TABLE_PERSON | | TABLE_PHONE | Col_Name | Col_name col_phoneID col_phoneNbr -------- | -------- ----------- ------------ Clark Kent | Clark Kent 1 111-111-1111 Bruce Wayne | Clark Kent 2 222-222-2222 Peter Parker | Peter Parker 2 333-333-3333 | Peter Parker 3 444-444-4444. Bruce Wayne 3 555-555-5555. Bruce Wayne 4 666-666-6666 

The col_name is actually an id field, but I use a name to demostrate. Using a SQL statement I have to get this

 col_name col_phoneNbr -------- ------------ Clark Kent 111-111-1111 Peter Parker 333 - 333-3333 Bruce Wayne 555-555-5555 

Phone_ID is actually a phone number such as main, fax, toll-free etc. Therefore, in my output I will have to call the number for each person, but in this way, if the type "1" is not available then choose type "2", if type is not "2", then "3" Select, etc. There are 6 types in my case, and their priority is some 4> 2> 1> 3> 5> 6, so I only want to be able to select the number available first, and if neither .

I did the previous project using a very complicated method, where I select "1" and all types of "2" while "1" filtering using the "no no" clause Type and then connecting two sets together. Actually, it ended like a 4 set, and I was pulling out for 1000+ records, it ran really slow.

I should mention that this is MS SQL Server 2000 , So I can not use any of 2005's features.

Thank you in advance!