TharinduLucky TharinduLucky - 6 months ago 8
SQL Question

Mysql query inside a query

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

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
. . .