SQL 连接“向上”两张桌子

发布于 2024-09-02 03:45:22 字数 2161 浏览 11 评论 0原文

我正在尝试使用联接创建一个中等复杂的查询：

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

我想用“part_list”.“name”替换 SELECT 语句中的“parts”.“type_id”，其中我需要在两个表之间强制执行的关系是`part_list`.`id` = `parts`.`type_id`。另外，我必须使用连接，因为在某些情况下 `history`.`part_id` 可能为 NULL，这显然不是有效的部件 ID。我将如何修改查询来执行此操作？

以下是根据要求提供的一些样本日期：历史表：

_{（来源：ianburris.com）}

序列化零件表：

_{（来源：ianburris.com）}

零件表：

_{（来源：ianburris.com）}

零件列表表：

_{（来源：ianburris.com）}

而我想看到的是：

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

原文

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:

_{(source: ianburris.com)}

serialized_parts table:

_{(source: ianburris.com)}

parts table:

_{(source: ianburris.com)}

part_list table:

_{(source: ianburris.com)}

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流云如水 2024-09-09 03:45:22

这至少是在正确的轨道上...

如果您不想显示任何具有无效 ID 的部分，只需将 LEFT JOIN 更改为 INNER JOIN（它们将限制 NULL 值）

    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

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

回复收藏 0 原文

奶茶白久 2024-09-09 03:45:22

天哪，这些反引号让我的眼睛受伤了。

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

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

回复收藏 0 原文

~没有更多了~