php - 查询获取树上的类别和产品

Question

我有类别表:

和产品表:

我想查询得到一棵同时包含类别和产品的树，例如:如果类别的最后一级是类别的产品，则下图相同:

Answer 1

更新:

谢谢 @bradbury9

我尝试 JOIN 2 表并使用 UNION ALL，它对我有用:

SELECT *
FROM 
     (
          (
                SELECT NULL AS content_id, NULL AS question, NULL AS answer, node.*, 
                     (
                          SELECT COUNT(parent1.id) - 1
                          FROM faq_categories AS node1,
                          faq_categories AS parent1
                          WHERE node1.lft BETWEEN parent1.lft AND parent1.rgt AND node1.id = node.id
                          GROUP BY node1.name
                          ORDER BY node1.lft
                     ) AS level
               FROM `faq_contents` as content
               LEFT JOIN `faq_categories` AS node ON node.`id` = content.`category_id`
               LEFT JOIN `faq_categories` AS parent ON parent.`id` = content.`category_id`
               WHERE node.lft BETWEEN parent.lft AND parent.rgt
               GROUP BY node.id

           )
           UNION ALL
           (
                 SELECT content.id AS content_id, content.question, content.answer, node.*, 
                       (
                             CASE WHEN content.id IS NULL THEN
                             (
                                  SELECT COUNT(parent1.id) - 1
                                  FROM faq_categories AS node1,
                                       faq_categories AS parent1
                                  WHERE node1.lft BETWEEN parent1.lft AND parent1.rgt AND node1.id = node.id
                                  GROUP BY node1.name
                                  ORDER BY node1.lft
                              )
                              ELSE 3 END
                       ) AS level
                  FROM `faq_contents` AS content
                  RIGHT JOIN `faq_categories` AS node ON node.`id` = content.`category_id`
           )
   ) node
ORDER BY lft