How to JOIN category table for parents in SQL query?

Question

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.

Several examples already exist on Stack: http://stackoverflow.com/questions/5689635 to name one. The long and short of it is mySQL doesn't support hierarchical queries, so you'll have to write something. in Oracle they use connect by prior. SQL server DB2 use http://stackoverflow.com/questions/959804 (with) — xQbert, Dec 17 '11 at 02:40
Well, it seems to be a challenging issue. I thought there is a simple method that I've missed. — Googlebot, Dec 17 '11 at 02:44
sadly no; not in mysql; if you have a max number of levels then adding left (self) joins is the easiest; but that first link links off to other answers shwoing some common solutions. — xQbert, Dec 17 '11 at 02:45

score 2 · Accepted Answer · answered Dec 17 '11 at 03:52

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

score 1 · Answer 2 · answered Dec 17 '11 at 03:51

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.

How to JOIN category table for parents in SQL query?

2 Answers2

Linked