How to JOIN category table for parents in SQL quer

问题:

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

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

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 ?

NOTE: I use mysql with MyISAM engine.

回答1:

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

回答2:

If you're willing to limit the depth, you can use UNION to stack up JOINs to get what you want. Here's a 3-level deep implementation:

select * from posts
where category_id = ?
union
select * from posts p
join category c on c.category_id = p.category_id
where c.parent = ?
union
select * from posts p
join category c1 on c1.category_id = p.category_id
join category c2 on c2.category_id = c1.parent
where c2.parent = ?

You would pass the same variable in (for the category you're interested in) for all 3 placeholders.