How to select hierarchical records from two tables using join

Question

Actually I want to select matched records from two tables with hierarchical data from the first table

I have two tables:

1. tmpos_category table with columns (category_refid(pk),category_name,parent_ref_id). i am storing hierarchical data in it.

2. tmpos_menu_child table with columns (id(pk),title,category_ref_ids(fk)). category_ref_ids field from tmpos_menu_child table references the category_refid field of tmpos_category table.

this is a tmpos_category table with hierarchical categories

tmpos_menu_child table with category_ref_ids as fk refrances category(category_refid ) column

SELECT DISTINCT ct.category_name,ct.category_refid,ct.parent_ref_id
from tmpos_category ct
JOIN tmpos_menu_child tmc
ON ct.category_refid = tmc.category_ref_ids

Now My Question is when i join tmpos_category table and tmpos_menu_child table i will get all distinct matched category but i also want selected category parent records

Answer 1

If you can only have 2 levels in your hierarchy, i.e. a parent cannot itself have a parent, then two JOINs will do it.

SELECT i.id AS item_id, i.title AS item_title
     , c.category_refid, c.category_name
     , c.parent_ref_id, p.category_name AS parent_name
  FROM tmpos_menu_child i
  JOIN tmpos_category c ON c.category_refid = i.category_ref_ids
  LEFT JOIN tmpos_category p ON p.category_refid = c.parent_ref_id

If the hierarchy can be deep, you should use a hierarchical query, which in most DBMS's are done with a recursive CTE (common table expression).

WITH RECURSIVE Item_and_Cat (item_id, item_title, category_level,
                             category_refid, category_name, parent_ref_id) AS (
   SELECT i.id AS item_id, i.title AS item_title
        , c.category_refid, c.category_name
        , 1 AS category_level, c.parent_ref_id
     FROM tmpos_menu_child i
     JOIN tmpos_category c ON c.category_refid = i.category_ref_ids
   UNION ALL
   SELECT i.item_id, i.item_title
        , p.category_refid, p.category_name
        , i.category_level + 1 AS category_level, p.parent_ref_id
     FROM Item_and_Cat i
     JOIN tmpos_category p ON p.category_refid = i.parent_ref_id
)
SELECT item_id, item_title, category_refid, category_name, category_level
  FROM Item_and_Cat

Note: The RECURSIVE keyword is required for PostgreSQL and MySQL, but not allowed for Oracle DB and SQL Server.

---

UPDATE

From comment:

i want there parents as record(in seperate row)

To get parent records as separate rows, run the query twice and combine with UNION.

SELECT i.id AS item_id, i.title AS item_title
     , c.category_refid, c.category_name, 0 AS is_parent
  FROM tmpos_menu_child i
  JOIN tmpos_category c ON c.category_refid = i.category_ref_ids
UNION ALL
SELECT i.id AS item_id, i.title AS item_title
     , p.category_refid, p.category_name, 1 AS is_parent
  FROM tmpos_menu_child i
  JOIN tmpos_category c ON c.category_refid = i.category_ref_ids
  JOIN tmpos_category p ON p.category_refid = c.parent_ref_id