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