How to make a product catalog?

Question

PHP

Friends, hello! Please help who can.
There are 2 tables, 1 with product categories:
- id_category
-category_name
-parent_Id
And a second table with products:
Id_goods
Goods_name
Id_category
They are related to each other via id_category.
In the first table, in the fields where parent_id is empty - the parent category and it has child fields that will refer to this field. The output is almost something like this:
TVs
LCD TVs
Cryst TVs, etc.
Essence of the question: how to make a request so that at the output we get the answer the name of the product and the name of the parent category?
That is, for example like this:
TVs( this is a category_name with parent_Id = null) , LG TV ( goods name )
I can't make a request...
It turns out to separately select the name of the parent category and goods belonging to a particular category, and then I can't think of it :(
Many thanks to those who responded!

Answer 1

For two nestings, the query will look like this:

select c1.category_name, g.Goods_name
from goods as g
left join category as c on c.id_category = g.id_category
left join category as c1 on c1.id_category = с.parent_Id

For greater nesting, you need to unwind further, i.e. by analogy, add additional joins.

Answer 2

select c.category_name, c.parent_Id, g.Id_goods, g.Goods_name, c.id_category
from goods as g
left join category as c on c.id_category = g.id_category

Answer 3

MySQL does not know how to recursive queries, and with a large nesting there will be an extra load when fetching. Therefore, if categories are nested more than 1 level (TVs - LCD TVs - 3D TVs, and so on), it's easier to add the top_parent_id field to the categories table.