I want to ask something that I know that rather it is not possible.
I have a table (categories) that has a foreign key to thatself.(one->many).
I want to take all the chain of a category (A category and all the subcategories).It is possible with a single view?
I thing it is possible with a Stored Routine.
If the chain has a limited max depth (say 3), then you might join the table three times (LEFT OUTER JOIN). But it wouldn't be flexible.
If there's no fixed limit to the depth, it's not possible as far as I know.
joins up to 15 levels deep works quite effectively
see Categories and Subcategories