CODEWITHSUNDEEP
phpmysql
Tharindu Thisarasinghe
Tharindu Thisarasinghe

Reputation: 3998

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 ?

Upvotes: 1

Views: 61

Answers (3)

James A
James A

Reputation: 775

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;

Upvotes: 1

Adam Silenko
Adam Silenko

Reputation: 3108

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

Upvotes: 1

Related Questions