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 ?
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
. . .
to select rows filtered by condition on secend table use join in FROM clause or subquery in condition with IN or EXISTS function. To compare field with some string you can use LIKE operator.
If you are filtering based on parent_category -
SELECT b.*, a.name FROM movies b
LEFT JOIN categories a ON a.id = b.category
WHERE a.parent_category = 1;