mysql query for children and grandchildren

Question

column view

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

table core.category

Id      ParentId   Name

1       null       ELECTRONICS
2       1          TELEVISIONS
3       2          TUBE
4       2          LCD
5       2          PLASMA
6       1          GAME CONSOLES
7       1          PORTABLE ELECTRONICS
8       7          MP3 PLAYERS
9       8          FLASH
10      7          CD PLAYERS
11      1          2 WAY RADIOS
12      11         FRS

If I SELECT 1,

result is (shows children and grandchildren) expect that there is more than 1 grandparent(ELECTRONICS)

+-----+
| id  |
+-----+
| 1   |
| 2   |
| 3   |
| 4   |
| 5   |
| 6   |
| 7   |
| 8   |
| 9   |
| 10  |
| 11  |
| 12  |
+-----+

If I select 2

result must be

+-----+
| id  |
+-----+
| 2   |
| 3   |
| 4   |
| 5   |
+-----+

i got this for selected category and his children ( need more to output grandchildren)

module.exports.categorySearch = function(searchCategory, cb) {
  var query = 'SELECT category_id \
   FROM core.category AS c \
   WHERE category_id = $1::integer
UNION
   SELECT category_id \
   FROM core.category AS c \
   WHERE category_ParentId = $1::integer
;';
  var map = function(err, result) {
     if(err) {
        return cb(err, null);
      }
      var list = [];
      result.rows.forEach(function(row, i) {
        list.push(new Category(row));
      });
      cb(null, list);
    };
  repository.queryDb(query, [searchCategory], map);
};

result is

+-----+
| id  |
+-----+
| 1   |
| 2   |
| 6   |
| 7   |
| 11  |
+-----+

what should i add to my select statement

is this in a node server...if not what does javascript have to do with it? — charlietfl, Sep 30 '15 at 05:05
Also in addition to this: do you only want to go 2 steps or through ALL steps? (that means if there are then also grand grand parents,....) ? — Thomas, Sep 30 '15 at 05:26
i want to show all under the selected category, including the selected category — dummy js, Sep 30 '15 at 06:08
That would mean then that you want to have a recursive result of the category and all that is under it (the question title is a bit misleading there). Additionally as some mentioned do you use just mysql or also javascript (as you used the tag there). and if javascript: What code do you have so far there and what frameworks (if any like jquery, angularjs,...) are you using for javascript? These would all be important informations there. — Thomas, Sep 30 '15 at 06:12
Do you want `category and its subcategories only` OR `category with subcategory and sub-subcategory and so on` — Abhishek Sharma, Sep 30 '15 at 06:17
do you have any code written so far? (aka what statements do you have so far for getting the data from the database? (not sure what options angular gives as I never used it myself so far, but that info would also be helpful). and if you want to use angular for this you should add angularjs (if it exists as tag) to the tags. — Thomas, Sep 30 '15 at 06:23
i just want to improve my select statement so i can get the children and grandchildren and so on, recursive i think — dummy js, Sep 30 '15 at 06:31
so something like this? http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql — Thomas, Sep 30 '15 at 06:43
cant understand that wait ill have new update, i can output , children now but not grand children and so on — dummy js, Sep 30 '15 at 06:46

dummy js · Accepted Answer · 2015-09-30T07:55:23.293

WITH RECURSIVE categorytree AS (
  SELECT c.category_id, 0 as depth
  FROM core.category AS c
  WHERE c.category_id = $1::integer

  UNION ALL

  SELECT c.category_id, t.depth + 1 as depth
  FROM core.category AS c
  INNER JOIN categorytree t
    ON t.category_id = c.category_ParentId
)

SELECT category_id FROM categorytree

shows the selected and children and grandchildren and so on...

+-----+
| id  |
+-----+
| 1   |
| 2   |
| 3   |
| 4   |
| 5   |
| 6   |
| 7   |
| 8   |
| 9   |
| 10  |
| 11  |
| 12  |
+-----+

mysql query for children and grandchildren

1 Answers1