How to make such a conclusion from the database?

the Categories table
id
cat_name
cat_url
parent

and table Products
..
cat_id
..
..

Choose a list of goods according to cat_url.
$sql = "SELECT" . parent::PREFIX . $this->table . ".*, categories.cat_url, categories.cat_name
 FROM {$this->table} INNER JOIN categories
 ON categories.id = {$this->table}.cat_id WHERE categories.cat_url = :category_url";

What happens is in the category of "Paint" there are only sub-categories, and in it there are no goods. If you follow the link "Paint", we get the error.
How to display all products of all subcategories of this category?
July 9th 19 at 12:50
2 answers
July 9th 19 at 12:52
Solution
for level nesting category->subcategory
you can do this:
SELECT products.*, categories.cat_url, categories.cat_name
FROM products INNER JOIN categories
ON categories.id = products.cat_id
WHERE categories.cat_url = :category_url OR
categories.parent in (SELECT id FROM categories WHERE cat_url = :category_url)

but if You have a nesting level of more I recommend to get acquainted with the approach of the Nested set or other approaches to working with trees
July 9th 19 at 12:54
Use LEFT JOIN
SELECT cat.cat_url, cat.cat_name, prod.name 
FROM categories cat 
LEFT JOIN products prod 
ON prod.cat_id = cat.id

Find more questions by tags MySQLPHP