How to write complex SQL query?

Question

MODX

It is necessary to sort the goods in the table, according to the characteristics in another table.
Here's what happened:
SELECT content.id, content.pagetitle, content.parent, tv.id, tv.tmplvarid, tv.contentid, tv.value FROM modx_site_content AS content RIGHT JOIN modx_site_tmplvar_contentvalues ​​AS tv ON content.id = tv.contentid and tv.tmplvarid = 20 and tv.value = 4 or content.id = tv.contentid and tv.tmplvarid = 20 and tv.value = 6 or content.id = tv.contentid and tv.tmplvarid = 19 and tv.value = 10 WHERE parent = 22923
ORDER BY `content`.`id` ASC
Sorting problem, how can I check if the product is equal to the selected parameter?

Answer 1

The question is not entirely clear.
I'll tell you right away.
Despite the fact that the conditions are written in the same way, it is necessary to separate the conditions for linking two tables and the filtering/selection conditions. Logically, the selection conditions filter the final array of rows obtained after linking all tables. Technically, filtering can be performed earlier.
That. the connection conditions in this task should be left under the ON instruction, and the filtering conditions should be moved to the WHERE clause.
And yes, (tip #1) don't forget to write the table alias before the field name, even if there is no such field in another table. After all, tomorrow it may appear there and the code will not work. Learn (tip number 2) to come up with short aliases to the table. A better system for obtaining short aliases. This will come in handy in the future when the number of tables in the system and in the query starts to increase.
Perhaps I misunderstood the essence of the question, but something similar to

SELECT
  c.id
  , c.pagetitle
  , c.parent
  , tv.id
  , tv.tmplvarid
  , tv.contentid
  , tv.value 
FROM modx_site_content AS c 
RIGHT JOIN modx_site_tmplvar_contentvalues AS tv ON c.id = tv.contentid
WHERE c.parent = 22923
  AND ( tv.tmplvarid, tv.value ) IN ( ( 20, 4 ), ( 20, 6 ), ( 19, 10 ) )
ORDER BY c.id ASC

And the last. It seems to me 99% that based on the names of the tables, it should not be RIGHT, but LEFT JOIN.