Imagine you have an online store. The depth of subcategories isn't limited, so you may get some items pretty close to the top and some all the way at the bottom. You want to display breadcrumbs, something like:
You have a table that stores your items, something like:
You have table for categories:
If catParent = 0 it's a root category.
Question is - what's the most efficient way to build a SELECT query (or stored procedure, or - whatever you think is good) to get a full breadcrumb path out of database.
The only restriction - it must be solved on the side of the database, not in some code.
Any ideas? My dev teammates already puzzled, so I am bringing this here