如何在SQL查询中加入父类的类表？ [英] How to JOIN category table for parents in SQL query?

问题描述

我有一个表的帖子作为（id，category_id，...），然后 JOIN 它与类别表为（category_id，category_name，parent，.. 。）ON category_id 。父级是另一个类别的category_id。例如：

I have a table for posts as (id, category_id, ...), then JOIN it with the category table as (category_id, category_name, parent, ...) ON category_id. Parent is the category_id of another category. For example:

category_id    category_name      parent
1              Computer Science   NULL
2              Web Technology     1
3              Mathematics        NULL
4              PHP                2

我如何 JOIN 帖子和类别表以读取帖子的父类别。例如，

How can I JOIN posts and category tables to read parent categories of a post too. For example,

id     category_id    post_title
44     4              something

使用简单的 JOIN ON category_id，我只能获得PHP类别;我怎么能得到父母也计算机科学> Web技术> PHP ？

With simple JOIN ON category_id, I can get only PHP as category; how can I get the parents too as Computer Science > Web Technology > PHP ?

注意：我使用mysql和MyISAM引擎。

NOTE: I use mysql with MyISAM engine.

推荐答案

只需对额外元素执行额外的连接，但将IT作为LEFT连接，因为并非所有类别都具有父类别，并且不想排除这些类别。

Just do an additional join for the extra element, but have IT as a LEFT join as not all categories have a parent category and you don't want to exclude those.

select
      P.ID,
      P.Post_Title,
      P.Category_ID,
      C.Category_Name as FirstCat,
      C.Parent,
      COALESCE( C2.Category_Name, ' ' ) as ParentCategory
   from
      Posts P
         JOIN Categories C
            on P.Category_ID = C.Category_ID
            LEFT JOIN Categories C2
               on C.Parent = C2.Category_ID
   where
      AnyFiltering

这篇关于如何在SQL查询中加入父类的类表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！