0

I need to fetch all category id's from a database that originate from a parent category id. The table structure is

CREATE TABLE `categories` (
    `categories_id`      int(11)     NOT NULL,
    `categories_image`   varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `parent_id`          int(11)     NOT NULL DEFAULT '0',
    `sort_order`         int(3)      DEFAULT NULL,
    `date_added`         datetime    DEFAULT NULL,
    `last_modified`      datetime    DEFAULT NULL,
    `categories_status`  tinyint(1)  NOT NULL DEFAULT '1'

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

And a sample of the data for that table is

INSERT INTO `categories` (`categories_id`, `categories_image`, `parent_id`, `sort_order`, `date_added`, `last_modified`, `categories_status`) VALUES
(151, '36M.jpg', 0, 0, '2018-09-20 14:31:23', '2018-09-25 15:56:07', 1),
(152, 'aldridge/AS6505.jpg', 151, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:09', 1),
(153, 'aldridge/L19329.jpg', 152, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:09', 1),
(154, 'aldridge/AS1068.jpg', 153, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(155, 'aldridge/AS11417.jpg', 154, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(156, 'aldridge/AS10917.jpg', 154, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(157, 'aldridge/AS11315.jpg', 153, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:09', 1),
(158, 'aldridge/10292.jpg', 157, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:09', 1),
(159, 'aldridge/L17107.jpg', 153, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(160, 'aldridge/AS1017.jpg', 159, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(161, 'aldridge/L19177.jpg', 152, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(162, 'aldridge/AS2605.jpg', 161, 0, '2018-09-20 14:35:03', '2018-09-27 14:29:10', 1),
(163, 'aldridge/L19329.jpg', 153, 0, '2018-09-20 14:35:04', '2018-09-27 14:29:09', 1);

I need ALL category id's that are children of category 151.

As a basic example, 159 is a child of 153, which is a child of 152, which is a child of 151, so I would want a result of 159, 153, 152, 151.

Short of just doing 

SELECT categories_id FROM categories WHERE `parent_id` = 151;

and then another SELECT query using the result of that query, and another, and another, until I get no further results, is there a way to perform such a query in a single command?

Dai
  • 141,631
  • 28
  • 261
  • 374
Steph3071
  • 321
  • 2
  • 11
  • You should check out [Common Table Expressions](https://dev.mysql.com/doc/refman/8.0/en/with.html), you can solve the problem with that. – Johan Sep 28 '18 at 20:02
  • Assuming OP has the latest version of MySQL... – Zack Sep 28 '18 at 20:06
  • @Barmar, had i known it was referred to as a "hierarchical recursive query" then I would have looked for such questions, but I didn't, hence my post. I'll read that to see if it helps in any way. – Steph3071 Sep 28 '18 at 20:09
  • I actually found it by searching for `[mysql] all children`. One of the questions that this found had a link to it. – Barmar Sep 28 '18 at 20:10
  • @Barmar. Good spot. That answers my question perfectly. Thanks – Steph3071 Sep 28 '18 at 20:16

0 Answers0