MySQL - "select" query inside a problem with IN (...) (=> Hierarchical query)

Question

Before you start voting and refuse my reply, please note that I am a complete newbie :). I was already looking for an answer, but it seems to be quite specific.

(as I understand it as a programmer, if in select statements, where for loops, it will look like a loop inside a loop inside a loop: D)

The question is about optimizing my SQL query, because it takes a few seconds to get the data.

SQL crash:

1st. inquiry

SELECT * 
FROM r_submenuitems 
WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems WHERE item_id = 1068)

this query gets a list (the list in the second query, to be precise), and I want to go one step higher. That is, use this list again in the IN clause of the query and select a new list.

Second. inquiry

SELECT submenu_id 
FROM r_submenuitems 
WHERE modifier1 IN (31050, 131050,3912, 103122, 103165, 7772, 7782)

---

To combine 2 queries into 1, I did the following:

SELECT submenu_id FROM r_submenuitems 
WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems 
    WHERE modifier1 IN (SELECT submenu_id FROM r_submenuitems 
        WHERE item_id = 1068))

.

( ), ? , - , 2 , "--"

edit: (31050, 131050,3912, 103122, 103165, 7772, 7782). . "--" , , , , .

Answer 1

Try

SELECT submenu_id  
  FROM r_submenuitems r1
           INNER JOIN r_submenuitems r2
             ON r1.modifier1 = r2.submenu_id
 WHERE r2.item_id = 1068

, . , .

SELECT submenu_id FROM r_submenuitems 
 WHERE modifier1 IN (SELECT submenu_id                   --All this IN statement is
                       FROM r_submenuitems               --unnecessary you are already
                      WHERE modifier1 IN ( SELECT submenu_id -- doing on the inner IN
                                             FROM r_submenuitems 
                                            WHERE item_id = 1068
                                         )
                    )

2

, , mysql , , , , , , JOIN .

( ) MYSQL.

( )

SELECT r1.submenu_id  
  FROM r_submenuitems r1 
        INNER JOIN r_submenuitems r2 ON r1.modifier1 = r2.submenu_id
        INNER JOIN r_submenuitems r3 ON r2.modifier1 = r3.submenu_id
 WHERE r3.item_id = 1068