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

Question

Sorry about a vague title, but here's what i am trying to do.

I have 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 am using a name to demostrate. Here's what i want to get by using one SQL statement

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

The phone_ID is actually a type of phone number such as main, fax, toll-free, etc. So in my output i would like to have a phone number for each individual, but be selected in a such a way that if type "1" is not available, select type "2", if type "2" is not there, select type "3", etc. In my case there are 6 types, and their priority is something like 4 > 2 > 1 > 3 > 5 > 6, so i would like to be able to select only the first available number, and NULL if neither is there.

I did this in a previous project by using a very convoluted method where i would select all type "1" and then all type "2" while filtering type "1" using a "NOT IN" clause and then UNIONing the two sets together. Actually, it ended up being like 4 sets, and for the 1000+ records i was pulling out, it ran really slow.

I should mention that this is MS SQL Server 2000, so i can't use any 2005 features.

Thanks in advance!

Answer 1

The simple case where the priority of the phone ID is its numeric value:

select pers.col_Name, ph2.col_phoneNbr
from TABLE_PERSON pers
inner join (select col_Name, min(col_phoneID) from TABLE_PHONE group by col_Name) as ph1 on ph1.col_Name=pers.col_Name
inner join TABLE_PHONE ph2 on ph2.col_Name=pers.col_Name and ph2.col_PhoneID=ph1.col_PhoneID

Now, let's create a mapping to handle the priority <-> ID: Try this:

create table phone_prio (phone_ID int, prio int);

insert into phone_prio (phone_ID, prio) values (4, 1);
insert into phone_prio (phone_ID, prio) values (2, 2);
insert into phone_prio (phone_ID, prio) values (1, 3);
insert into phone_prio (phone_ID, prio) values (3, 4);
insert into phone_prio (phone_ID, prio) values (5, 5);
insert into phone_prio (phone_ID, prio) values (6, 6);

and update the naive case:

select pers.col_Name, ph2.col_phoneNbr
from TABLE_PERSON pers
inner join (
    select col_Name, min(pr.prio) as prio
    from TABLE_PHONE ph
    inner join phone_prio pr on pr.phone_ID=ph.col_phoneID
    group by col_Name
) as ph1 on ph1.col_Name=pers.col_Name
inner join phone_prio pr1 on pr.prio=ph1.prio
inner join TABLE_PHONE ph2 on ph2.col_Name=pers.col_Name and ph2.col_PhoneID=pr1.phoneID

Answer 2

SELECT
    pe.col_Name,
    (SELECT TOP (1) 
         ph.col_PhoneNbr
     FROM TABLE_PHONE ph
     WHERE pe.col_Name = ph.col_Name
     ORDER BY
         CASE col_phoneID
             WHEN 4 THEN 1
             WHEN 2 THEN 2
             WHEN 1 THEN 3
             WHEN 3 THEN 4
             WHEN 5 THEN 5
             WHEN 6 THEN 6
         END
    ) as col_phoneNbr
FROM TABLE_PERSON pe

Answer 3

You want to use the HAVING clause

GROUP by col_phoneID
HAVING col_phoneID = MAX(col_phoneID)

Obviously the MAX isn't what you want so you may have to add some things to your query But I hope it points you in the right direction.