step by step, first pick out the point with root node 7 in the Structure table:

SELECT GROUP_CONCAT(lv SEPARATOR ',') AS StructureIds
     FROM
       (SELECT @root:=
          (SELECT GROUP_CONCAT(id SEPARATOR ',')
           FROM STRUCTURE
           WHERE FIND_IN_SET(StructureParentId, @root)) AS lv
        FROM STRUCTURE
        JOIN
          (SELECT @root:=7) tmp
        UNION (SELECT 7)) a
;

is easy to understand, that is, starting from the first item of the table, each item is assigned once @ root, condition is that the parent node is in the previous @ root, and then you get the tmp table, and finally concatenate each @ root of the tmp table.

then you can take this string to the Standard table to query:

SELECT *
FROM Standard
WHERE StructureID IN
    (SELECT GROUP_CONCAT(lv SEPARATOR ',') AS StructureIds
     FROM
       (SELECT @root:=
          (SELECT GROUP_CONCAT(id SEPARATOR ',')
           FROM STRUCTURE
           WHERE FIND_IN_SET(StructureParentId, @root)) AS lv
        FROM STRUCTURE
        JOIN
          (SELECT @root:=1) tmp) a)
;

is the lookup function FIND_INT_SET.

try it. I hope it can help you.

WITH locs (id,StructureCode,StructureName,StructureParentId)

        AS
        (
        SELECT Id,StructureCode,StructureName,StructureParentId FROM Structure WHERE StructureParentId=$id
        UNION ALL
        SELECT A.id,A.StructureCode,A.StructureName,A.StructureParentId FROM Structure A,locs B ON A.StructureParentId = B.id
        )
        
        select s.Id , st.StructureCode,s.StandardName, s.StandardNumber , s.PublishDate,s.ImplementDate ,s.OldStandardNumber from 
        Standard s ,Structure st LEFT JOIN StdFile sf ON s.StandardNumber=sf.StandardNumber WHERE s.StructureID=$id OR s.StructureID IN (SELECT id FROM locs) group by s.Id 

the test data is as good as you want