SQL query with nested/hierarchial relationship table

Question

I have these two tables:

Table "items":
╔════╦══════════════╦═══════╗
║ ID ║     NAME     ║ TYPE  ║
╠════╬══════════════╬═══════╣
║  1 ║ Home         ║ House ║
║  2 ║ Livingroom   ║ Room  ║
║  3 ║ Kitchen      ║ Room  ║
║  4 ║ Pots         ║ Item  ║
║  5 ║ Pans         ║ Item  ║
║  6 ║ Table        ║ Item  ║
║  7 ║ Away         ║ House ║
╚════╩══════════════╩═══════╝

Table "relationships":
╔════╦═══════════╦═══════════╗
║ ID ║  ITEM_ID  ║ PARENT_ID ║
╠════╬═══════════╬═══════════╣
║  1 ║     2     ║     1     ║
║  2 ║     3     ║     1     ║
║  3 ║     4     ║     3     ║
║  4 ║     5     ║     3     ║
║  5 ║     6     ║     2     ║
╚════╩═══════════╩═══════════╝

And I am building the following array in php:

$arr = array( 
  '1' => array(
    'name' => 'Home',
    'rooms' => array(
      '2' => array(
        'name' => 'livingroom',
        'items' => array(
          '6' => 'Table'
        )
      ),
      '3' => array(
        'name' => 'kitchen',
        'items' => array(
          '4' => 'Pots',
          '5' => 'Pans'
        )
      ),
    ),
  );
  '7' => array(
    'name' => 'Away',
    'rooms' => array(
    ),
  );
);

Currently the way this is done is by first doing a query on the items table for the houses:

SELECT
  id,
  name
FROM items
WHERE type = "House"';

Then a query on the relationships table that left joins the items table on item_id to get the name and the type.

SELECT
  i.id        AS id,
  i.name      AS name,
  i.type      AS type,
  r.parent_id AS parent
FROM relationships r
LEFT JOIN items i
ON r.element_id = i.id';

And finally three foreach loops are used to build the array. One to structure the houses from the first query. then another to separate rooms and items, and a third to combine it all into the final array.

While it works I'm sure a better solution is possible for the query, such as combining the two into one for example. How could this query be combined into one and the array be built with as few loops as possible?

Sure, but could you show me an example of how tabular form looks like? — Jay
– Jay, Commented Dec 17, 2017 at 5:31
I meant as a table. You are asking for an SQL query. Based on your sample data provide the result of such query. — PM 77-1
– PM 77-1, Commented Dec 17, 2017 at 20:36

Sloan Thrasher · Accepted Answer · 2017-12-17 06:04:37Z

1

If you only have 3 nesting levels, this query will work to return the parents and their children:

SELECT 
    a.`ID`,
    a.`NAME`,
    a.`TYPE`,
    c.`NAME`,
    c.`TYPE`,
    e.`NAME`,
    e.`TYPE`    
FROM `items` a
JOIN `relationships` b
ON a.`ID` = b.`ITEM_ID`
JOIN `items` c
ON b.`PARENT_ID` = c.`ID`
JOIN `relationships` d
ON b.`PARENT_ID` = d.`ITEM_ID`
JOIN `items` e
ON d.`PARENT_ID` = e.`ID`;

answered Dec 17, 2017 at 6:04

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query with nested/hierarchial relationship table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related