Lets say I have a table called Tags with an id columnm name column, and a parent_id column. Many tags are nested using the parent_id column. How would I check if Tag A has Tag B as a non-direct child efficiently.
Previously I have selected all tags that have a parent_id of the current tag and then got the result and repeated for any child elements.
How would I do this more efficiently to get all tags that match a search and is a direct or non-direct child.
Thanks for the help, Jason
Rather than discuss in the comments... here is what I would recommend:
If you want to stick with MySQL but can play with the structure of your database then absolutely this "Closure Table" pattern suggested by Bill Karwin is the way to go. It allows you to keep your data in a flat table design while abstracting the multi-level tree structure into a separate table for easy data extraction.
If you want to try a different Relational Database System then you might try SQL Server Express which is free from Microsoft. In full disclosure, I don't use this so I don't know what functionality is excluded (and I'm sure something is otherwise you wouldn't get it for free). So please do some research to make sure Recursive Common Table Expressions (CTEs) are available. If they are then you can use Pinal Dave's blog post for recursive SQL technique using CTEs.
Otherwise if you only think you will only ever have a handful of levels to work with, you can use the original suggestion and hardcode the number of levels.