SQL joins "going up" two tables

Question

I'm trying to create a moderately complex query with joins:

SELECT `history`.`id`,  
       `parts`.`type_id`, 
       `serialized_parts`.`serial`, 
       `history_actions`.`action`, 
       `history`.`date_added`
FROM `history_actions`, `history`
LEFT OUTER JOIN `parts` ON `parts`.`id` = `history`.`part_id`
LEFT OUTER JOIN `serialized_parts` ON `serialized_parts`.`parts_id` = `history`.`part_id`
WHERE `history_actions`.`id` = `history`.`action_id` 
  AND `history`.`unit_id` = '1' 
ORDER BY `history`.`id` DESC

I'd like to replace `parts`.`type_id` in the SELECT statement with `part_list`.`name` where the relationship I need to enforce between the two tables is `part_list`.`id` = `parts`.`type_id`. Also I have to use joins because in some cases `history`.`part_id` may be NULL which obviously isn't a valid part id. How would I modify the query to do this?

Here is some sample date as requested: history table:

serialized_parts table:

parts table:

part_list table:

And what I want to see is:

id  name           serial    action   date_added
4   Battery        567     added    2010-05-19 10:42:51
3   Antenna Board  345     added    2010-05-19 10:42:51
2   Main Board     123     added    2010-05-19 10:42:51
1   NULL           NULL    created  2010-05-19 10:42:51

Answer 1

This would at least be on the right track...

If you're looking to NOT show any parts with an invalid ID, simply change the LEFT JOINs to INNER JOINs (they will restrict NULL values)

    SELECT `history`.`id`  
         , `parts`.`type_id`
         , `part_list`.`name`
         , `serialized_parts`.`serial`
         , `history_actions`.`action` 
         , `history`.`date_added`
      FROM `history_actions`
INNER JOIN `history` ON `history`.`action_id` = `history_actions`.`id`
 LEFT JOIN `parts` ON `parts`.`id` = `history`.`part_id`     
 LEFT JOIN `serialized_parts` ON `serialized_parts`.`parts_id` = `history`.`part_id`
 LEFT JOIN `part_list` ON `part_list`.`id` = `parts`.`type_id`
     WHERE `history`.`unit_id` = '1' 
  ORDER BY `history`.`id` DESC

Answer 2

Boy, these backticks make my eyes hurt.

SELECT
  h.id,  
  p.type_id, 
  pl.name,
  sp.serial, 
  ha.action, 
  h.date_added
FROM
  history                     h
  INNER JOIN history_actions ha ON ha.id       = h.action_id
  LEFT JOIN parts             p ON p.id        = h.part_id
  LEFT JOIN serialized_parts sp ON sp.parts_id = h.part_id
  LEFT JOIN part_list        pl ON pl.id       = p.type_id
WHERE 
  h.unit_id = '1' 
ORDER BY
  history.id DESC