Getting values for nulls from two different tables?

I have a large table (main) like so

person_id    fee        ref_id    <otherfields>
1           foo        23
1            bar        null
1            abc        23
2            xyz        34

Many of the values for ref_id are null which I need to retrieve. Btw, I need to select, not update. I have t开发者_开发问答wo other tables d1, d2 that contain the necessary missing ref_id (along with different fields) and they can be referenced using the person_id. The person_id might not exist in one of the tables (e.g. it might not be in d1, but it is in d2)

I can fill in some of the ref_id using one of the tables, but how would I then use the other table to retrieve the values for the remaining nulls? Union sounds right to me? But I'm struggling with it since it's giving me more rows than I originally had, which can't be right.

SQL Server 2008

edit: d1,d2 have the same (person_id,ref_id) tuple

You want a join, and you probably want to use COALESCE, like:

SELECT person_id, fee,
   COALESCE (m.ref_id, d1.ref_id, d2.ref_id) as 'ref_id',
   ...
FROM table t
LEFT JOIN d1
  ON d1.person_id = t.person_id
LEFT JOIN d2
  ON d2.person_id = t.person_id
WHERE ...

This will give you the first Non-NULL value for ref_id from those 3 tables.

Edit:

To clarify what you were thinking, a UNION connects data sets or tables vertically, as in

SELECT 1,2,3
UNION
SELECT 4,5,6

Will give you

1,2,3
4,5,6

A JOIN connects tables by the row, linking a row in one table to a row in another table to "extend" the row out with values from both tables.

JNK's answer is the best, I think. But just to show another approach, you could do it as a single join with a UNION. Depending on the data set, one method or the other may be more efficient.

SELECT person_id, fee,
   COALESCE (m.ref_id, d.ref_id) as 'ref_id',
   ...
FROM table t
LEFT JOIN (SELECT person_id, ref_id FROM d1 UNION ALL SELECT person_id, ref_id FROM d2) d
  ON d.person_id = t.person_id

If d1 and d2 have rows for the same person_id, this will return more rows than are in the original table. If they have the same (person_id,ref_id) tuple, then changing the UNION ALL to a simple UNION should address that problem; otherwise, you need to be more specific in your question since you could get different results from d1 or d2.

Something like below. Please note you may still get more rows than in main table if any of d1 or d2 has multiple rows for same person_id. If not, it should not give you more rows.

Select
      m.*
    , d1.*
    , d2.*
From
    main m
Left Join
    d1
On
    m.person_id = d1.person_id
    And
    m.ref_id Is Null
Left Join
    d2
On
    m.person_id = d1.person_id
    And
    m.ref_id Is Null
    And
    d1.ref_id Is Null