Mysql查询里面的查询

Question

First, apologies if the title doesn't match the question. Well, the problem is how to build this query...

I have a table called category It contains categories of my stuff(movies). It's like this...

--------------------------------
ID | name   | parent_category
--------------------------------
1  | love   | 0
2  | action | 0
3  | fear   | 0
4  | passion| 1
5  | danger | 2
6  | death  | 3
--------------------------------

So, as you see, each category has a parent category. Except the first 3. They're parents.

And movies table is like this...

--------------------------------
ID | name   | category
--------------------------------
1  | aaaa   | 1
2  | bbbbbb | 2
3  | cccc   | 2
4  | ddddddd| 1
5  | eeeeee | 3
6  | fffff  | 3
--------------------------------

So, what i want to do is, to select movies by parent category. Which means if I click category, love, it should select all the movies of categories that having love as the parent category.

So, how to write this in a single query ?

Answer 1

If the parents are only one level deep, then you can use joins:

select m.*,
       coalesce(cp.id, c.id) as parent_id,
       coalesce(cp.name, c.name) as parent_name
from movies m left join
     categories c
     on m.category = c.id left join
     categories cp
     on c.parent_category = cp.id;

Actually, if you only want the id, you don't need two joins:

select m.*,
       (case when c.parent_id > 0 then c.parent_id else c.id end) as parent_id
from movies m left join
     categories c
     on m.category = c.id ;

Or, more simply:

select m.*, greatest(c.parent_id, c.id) as parent_id
. . .