Q:

SQL, how to get parent items of items that match a search query

I am adding search functionality bar to an existing application.  Right now, a query is built based on user input.  It returns items from a flat table and builds a hierarchal array of objects on the front end based on parentID and ID fields from the table.
Right now, the search works as follows. It returns items that match the search input.  If the user searches potato,
select foo.* from ... AND foo.name like "%$potato%" AND ...

How would I also return the item who's ID matches potato's parentID so I can retain the hierarchy structure on the front end during a search?
The end goal is to have both items that match the search themselves and any parent items that contain children that match the search be returned (but excluding other children that do not match.) There are potentially infinite levels of hierarchy, so children can have children.  Because of this I would assume some level of recursiveness is needed but I'm not sure how I would do that.
It's worth noting that this is a fairly large web app and I'm not in a position where I can change just anything, so I have to work around the existing query format.

A:

I believe what you are looking for is...all records matching %potato% plus all the records whose IDs matches the parentIDs of all %potato%.
You can add an OR condition that looks something like this..
select ... from ... AND (foo.name like "%potato%" OR foo.ID in (select parentID from foo where foo.name like '%potato%')...;

