A better way to do a many to many query, but with range of values from one table?

Looking for the best way to make this more efficient. I'm trying to create many to many queries but in this case I need to gather multiple values from categories table, and then find all the projects that contain those categories then get the projects that match full info from their table. (using a nested set with left right values for categories table).

Take in the ID of the category node and get left right values.

$sql = 'SELECT * FROM `categories_main`'
. ' WHERE `id`=:id';

Use the left right values found above to find all categories that have left right values within the range (all children categories)
```
$sql = 'SELECT `id`'
```
. ' FROM categories_main' . ' WHERE lft >= :lft AND rgt <= :rgt';
find project ID's in linking table that belong to one of the categories found above.
```
$inQuery = implode(',', array_fill(0, count($category_ids), '?'));

$sql = 'SELECT project_id, cat_id'
```
. ' FROM project_sites_categories_main' . ' WHERE cat_id IN(' . $inQuery . ')';

4.And then I take the project ids and actually get the project full project from their table.

    $inQuery = implode(',', array_fill(0, count($project_ids), '?'));
    $sql = 'SELECT * '
. ' FROM project_sites'
. ' WHERE id IN(' . $inQuery . ')';

Any direction would be great. I've found some tutorials and threads pertaining to many to many, but I cant seem to figure out how to make that work for a range of values from a table to get the linked files from the other.

thank you so much in advance for the help. I hope this is enough information.

Solution

It's really not much different from your query but I think using exists instead of in for not static values is better.

select * from project_sites  
    where id EXISTS  (select project_id from project_sites_categories_main 
                                    where cat_id EXISTS  (select id from categories_main 
                                                               where lft >= :lft and <= :rgt)

PS: if you want to performance improvment your query dont use select * just write columns what you need.