Mysql query inside a query

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 ?

select * from movies where category=1 .. it will display all movies in love category — kamal pal
– kamal pal, Commented May 29, 2016 at 14:23
No, actually, in the movies table, movies are always saved with a child category. — Tharindu Thisarasinghe
– Tharindu Thisarasinghe, Commented May 29, 2016 at 14:27

Gordon Linoff · Accepted Answer · 2016-05-29 14:29:25Z

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

answered May 29, 2016 at 14:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tharindu Thisarasinghe Over a year ago

Thanks! This seems little bit advanced stuff. Anyway I'll try. :)

Tharindu Thisarasinghe Over a year ago

In the answer, what is m means ?

Gordon Linoff Over a year ago

m is a table alias. It is an abbreviation for a table or subquery in the from clause. In some cases, it is a convenience for writing and reading queries; in other cases (for left joins) it is a requirement.

Adam Silenko · Accepted Answer · 2016-05-29 14:29:33Z

1

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.

answered May 29, 2016 at 14:29

Adam Silenko

3,1062 gold badges19 silver badges33 bronze badges

Comments

James A · Accepted Answer · 2016-05-29 14:36:41Z

1

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;

answered May 29, 2016 at 14:36

James A

7755 silver badges11 bronze badges

Collectives™ on Stack Overflow

Mysql query inside a query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related