How to make MySql select from multiple tables?

Question

MySQL

Hello.

There is such a scheme of tables:


Need:
Make a query in such a way as to select all records from the Resources table with values ​​from the Variable_values ​​table.

The result should look something like this:

resource
----id
----title
----variables
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
--------variable
------------id
------------title
------------values
----------------value
----------------value
----------------value
----childs
--------resource
------------id
------------title
------------variables
----------------variable
--------------------id
--------------------title
--------------------values
------------------------value
------------------------value
------------------------value
------------childs
и т.д.

The Resources table is tree-like. Depth is not limited. But for my task, I need to select all posts that have the following conditions and nesting:
Post with template Model
---- Post with template Design model
-------- Post with template Design Series

That is, nesting depth 3. template id's are known from the start.

I can handle this task like this:

SELECT * FROM Resources AS Models
LEFT JOIN Resources AS Designs ON Designs.parent_id = Models.id
LEFT JOIN Resources AS Batches ON Batches.parent_id = Designs.id
WHERE Designs.template_id = id шаблона дизайна модели
AND WHERE Batches.template_id = id шаблона серии дизайна

But then I don’t understand how to attach variables (Variables) and their values ​​​​(Variable_values). Joining is very costly in terms of query execution time.

I would like to have only one request, since it is necessary to use LIMIT for the selection, as well as different conditions for the selection (Where).

Please let me know what options you see.

Answer 1

SELECT * FROM Resources AS Models
LEFT JOIN Resources AS Designs ON (Designs.parent_id = Models.id AND Designs.template_id = id шаблона дизайна модели)
LEFT JOIN Resources AS Batches ON (Batches.parent_id = Designs.id AND Batches.template_id = id шаблона серии дизайна)
WHERE Models.parent_id IS NULL
AND Designs.id IS NOT NULL
AND Batches.id IS NOT NULL

Now stick JOINs from variables to this query.