SQL Left Join on Two Different Tables

Question

I have this table, Widget, that I need to join to a WidgetHistory table to gather historical data on my widgets. Each widget has a record in the Doodad and Thing tables as well. I am working with a sproc that returns each relevant version of the widget.

The tricky part is that the WidgetHistory table is relatively new and old widgets that have not been modified lately will not have corresponding records in the WidgetHistory table. When trying to retrieve each relevant version of the widget, I'm doing a left join between the WidgetHistory, Doodad, and Thing tables. For those widgets that don't have WidgetHistory records, I instead want to left join the Widget, Doodad, and Thing tables.

I've got this working for my very limited use cases but I'm leery of it as I think I will get unexpected results for widgets that have WidgetHistory records.

Here's the (simplified) query:

SELECT 
      w.ID,
      ISNULL(h.Title, w.Title)
      d.Version,
      t.Size
FROM dbo.Widget as w
LEFT JOIN dbo.WidgetHistory as h ON w.ID = h.ID
LEFT JOIN dbo.Doodad as d ON h.DoodadID = d.ID /*!*/ OR w.DoodadID = d.ID
LEFT JOIN dbo.Thing as t ON h.ThingID = t.ID /*!*/ OR w.ThingID = t.ID

The part I am leery about is what is after the ! for the last two joins. I know I could instead do a separate series of left joins solely between Widget, Doodad, and Thing, but that seems excessive (but maybe it isn't?).

Any thoughts on this or better strategies? Again, I am wanting to do a left join between WidgetHistory, Doodad, and Thing unless no WidgetHistory record exists for a widget, and in that case do the join between Widget, Doodad, and Thing.

Thanks.

Answer 1

I tried on MySQL and I think I got a solution. Hope it works on SQL Server as well:

SELECT 
  w.ID,
  ISNULL(h.Title, w.Title)
  d.Version,
  t.Size
FROM dbo.Widget as w
LEFT JOIN dbo.WidgetHistory as h ON w.ID = h.ID
LEFT JOIN dbo.Doodad as d ON d.ID = ISNULL(h.DoodadID, w.DoodadID) 
LEFT JOIN dbo.Thing as t ON t.ID = ISNULL(h.ThingID, w.ThingID)

I had to use on MySQL the function IFNULL, instead of ISNULL, but both seems to work the same way.

My test on MySQL:

Widget:

 ------ ----------- -------
| id   | doodad_id | value |
 ------ ----------- -------
|    1 |         1 |     1 |
|    2 |         5 |     5 |
|    3 |         3 |     3 |
|    4 |         6 |     6 |
 ------ ----------- -------

WidgetHistory

 ------ ----------- -------
| id   | doodad_id | value |
 ------ ----------- -------
|    2 |         2 |     2 |
|    4 |         4 |     4 |
 ------ ----------- -------

Doodad:

 ------ ---------
| id   | version |
 ------ ---------
|    1 |       1 |
|    2 |       2 |
|    3 |       3 |
|    4 |       4 |
|    5 |       5 |
|    6 |       6 |
 ------ ---------

And the query result:

 ------ ------------------------------------------- ---------
| id   | IFNULL(widgetHistory.value, widget.value) | version |
 ------ ------------------------------------------- ---------
|    1 |                                         1 |       1 |
|    2 |                                         2 |       2 |
|    3 |                                         3 |       3 |
|    4 |                                         4 |       4 |
 ------ ------------------------------------------- --------- 

As it works for the table Doodad, it should work to the table Thing as well.

I hope it works for you!

Answer 2

I'd use a union query myself. The top half would join to WidgetHistory and the bottom half would not.