Да, чтобы достичь этой цели, вам требуется использовать рекурсивный self-join.
Пример запроса может выглядеть следующим образом:
```
WITH RECURSIVE category_tree AS (
SELECT id, parent, name
FROM categories
WHERE id = :categoryId
UNION ALL
SELECT c.id, c.parent, c.name
FROM categories c
INNER JOIN category_tree ct ON c.id = ct.parent
)
SELECT p.name AS product_name, p.description, string_agg(ct.name, ', ') AS category_hierarchy
FROM products p
INNER JOIN category_tree ct ON p.category_id = ct.id
GROUP BY p.name, p.description;
```
В данном примере ":categoryId" - это параметр, который вы можете заменить на конкретный идентификатор категории, для которой вы хотите получить иерархию.
Этот запрос использует рекурсивный CTE (WITH RECURSIVE), который строит иерархию категорий, начиная с заданной категории и продолжая до корневой категории. Затем он объединяет таблицу продуктов с этой иерархией, используя LEFT JOIN, чтобы включить все продукты, даже если у них нет категорий или иерархии.
Чтобы узнать больше о рекурсивных CTE и других методах работы с иерархическими данными в SQL, я рекомендую изучить следующие ресурсы:
- Документация по вашей конкретной СУБД (например, PostgreSQL или MySQL) для получения более подробной информации о возможностях работы с иерархическими данными.
- Статьи и руководства об использовании рекурсивных CTE в SQL.
- Различные книги или онлайн-курсы по SQL, которые включают материалы о работе с иерархическими данными.