How to generate such sql query?

Question

SQL

Good afternoon!

SELECT * FROM oc_product WHERE product_id IN (
SELECT * FROM oc_product_to_category WHERE category_id IN (
SELECT category_id FROM oc_product_to_category WHERE product_id = '1'
)
) limit 6

How to write such sql query correctly. And most importantly, fast, without unnecessary load on the database
1) We get the product category ID from table 1
2) We take from the same table all products with the given category ID
3) We take all products from table 2

Answer 1

So write.
Only in subqueries it is not necessary to pull out all the fields, but only the id:

SELECT * FROM oc_product 
WHERE product_id IN (
    SELECT product_id FROM oc_product_to_category 
    WHERE category_id IN (
        SELECT category_id FROM oc_product_to_category 
        WHERE product_id = '1'
    )
) 
LIMIT 6