Refactoring MySQL query

We came up with this query:

SELECT tn1.* FROM tree_node tn1
WHERE tn1.type != "folder" AND tn1.parent IN (
    SELECT tn.id FROM tree_node tn
    LEFT JOIN tree_node_access tna ON tna.tree_node_id = tn.id
    WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
) OR tn1.id IN (
    SELECT tn.id FROM tree_node tn
    LEFT JOIN tree_node_access tna ON tna.tree_node_id = tn.id
    WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
)

But it must be possible to not have to repeat the subquery twice, as that query is exactly the same.

Is there a way to refactor this query?

Answer

You can replace the or with UNION and replace the subqueries with JOINs:

SELECT tn1.*
FROM tree_node tn1
INNER JOIN tree_node tn
ON tn1.parent=tn.id
LEFT JOIN tree_node_access tna
ON tna.tree_node_id = tn.id
WHERE tn1.type != 'folder'
AND (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
UNION
SELECT tn1.*
FROM tree_node tn1
INNER JOIN tree_node tn
ON tn.id=tn1.id
LEFT JOIN tree_node_access tna
ON tna.tree_node_id = tn.id
WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))