SQL Query that pulls rows in current table based on parent id optimization I am creating a user permission system. I will have my user permissions set up off of "objects" and not pages. For example, I have an EVENTS section. Within that section, there are FORMS, FEES, etc. I want to be able to handle permissions down to the child levels.

Here is my permission_objects table:

| id | object | parent_id |
+----+--------+-----------+
| 1  | EVENTS | NULL      |
| 2  | FORMS  | 1         |
| 3  | FEES   | 1         |


Here is my current query:

SELECT `up`.*
FROM `permission_objects` `po`
INNER JOIN `user_permissions` `up` ON `up`.`object`=`po`.`id` AND (`up`.`user_group`=2 OR `up`.`user_id`=17)
WHERE `po`.`object`='EVENTS' OR `po`.`parent_id`=(
  SELECT `id` FROM `permission_objects` WHERE `object`='EVENTS'
)


I really want to avoid doing a subquery. I thought that instead of having a parent_id I could have parent_object and have that contain the parent's object value. i.e. | 2 | FORMS | EVENTS |. That way I could change my WHERE clause to:

WHERE `po`.`object`='EVENTS' OR `po`.`parent_object`='EVENTS'


However I feel that keeping it as parent_id is a better approach. Is there a way to keep that but not have to have a subquery in my sql?
The sub-query as a concept is not a problem... you need to do that work, and this is a decent way to do it.

It would be better if it was expressed as a direct lookup... like:

WHERE `po`.`object`='EVENTS'
   OR exists (
      SELECT `sub`.`id`
      FROM `permission_objects` `sub`
      WHERE `sub`.`object`='EVENTS'
        and `sub`.`id` = `po`.`parent_id`
)


This sub-select is better because it can do an indexed lookup in the table in the sub-select based on the id column.

Apart from that, I don't see a better, or neater way to do it.