CODEWITHSUNDEEP
mysql
amir zaghari
amir zaghari

Reputation: 89

MySQL get related data by custom condition

I have three database tables like this :

movies_tbl(id,name,...)
category_relation_tbl(row,movie_id,category_id,is_delete, ...)
categories_tbl(id,name_fa,is_delete,...)

In this database, is_delete means the record is deleted. I have a query like this to get all movies with categories:

SELECT movies_tbl.* , GROUP_CONCAT(DISTINCT(categories_tbl.name_fa)) as category_names
                FROM movies_tbl
                LEFT JOIN category_relation_tbl ON category_relation_tbl.movie_id = movies_tbl.id  
                LEFT JOIN categories_tbl ON categories_tbl.id = category_relation_tbl.category_id
                WHERE movies_tbl.is_delete = 0 

                        GROUP BY movies_tbl.id

How should I get all movies with categories even they don't have any categories and also how should I check category_relation_tbl.is_delete = 0? When I check (category_relation_tbl.is_delete) MySQL returns only movies that have related data in category_relation_tbl!!

Upvotes: 2

Views: 97

Answers (1)

flyingfox
flyingfox

Reputation: 13506

You can add category_relation_tbl.is_delete = 0 after LEFT JOIN

SELECT movies_tbl.* , GROUP_CONCAT(DISTICT(categories_tbl.name_fa)) as category_names
      FROM movies_tbl
      LEFT JOIN category_relation_tbl ON category_relation_tbl.movie_id = movies_tbl.id  
      AND category_relation_tbl.is_delete = 0
      LEFT JOIN categories_tbl ON categories_tbl.id = category_relation_tbl.category_id 
      GROUP BY movies_tbl.id

Upvotes: 4

Related Questions