0

This question already has an answer here:

I have simple child-parent relation by saving parent_id. Is it possible to count which level each entry?

My DB looks like this:

enter image description here

As You can see from the image, for example the first item relation is:

  • Aston Martin
    • Vanquish
      • 10
    • V8...
    • DB9
    • Rapide

When I selecting Aston Martin I need to know that it is first record, when Vanquish is the second, and so on.

marked as duplicate by RolandoMySQLDBA mysql Mar 31 '16 at 17:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • you order the children 'Aston Martin' of based on what? the id? what is the 10 under 'Vanquish' supposed to mean? – redguy Mar 23 '16 at 9:33
  • Also, it would help a lot if you specified what output exactly you want to get, in tabular form. – redguy Mar 23 '16 at 10:11
  • You've no clear hierarchy there at all i.e. you have 16 but no 13-15? Or is there missing data? – Vérace Mar 23 '16 at 10:43
  • @redguy there is a chain like Aston Martin -> Vanquish -> 10. 10 is just a name of child. When i select item with id 85 I need to know what index it is. Currently it is 3 as it has two parents. – Kin Mar 23 '16 at 12:00
  • @Vérace there is a type field which could be different. – Kin Mar 23 '16 at 12:01
0

in my opinion structure not normalised, but if need resolve it as is

SELECT 
t1.id, t1.name,
CASE WHEN parent_id = 0 THEN
       1
ELSE
    (SELECT count(id)+1 from Table t2 WHERE t2.parent_id = t1.parent_id and t2.id < t1.id) 
END AS seq_no 
    FROM Table t1

there are many modifications possible, depending from what finally You need, such as if need select seq_no for single name

  • 1
    It returns for 10 seq_no = 1, but it should be 3 as chain is like Aston Martin -> Vanquish -> 10 – Kin Mar 23 '16 at 11:58
0

I don't think you can do it with a simple query. Calculating the nesting level is like visiting a tree, that it is a recursive structure. So some kind of loop (or recursion) is needed. I have thought a possible solution with a function and one with a stored procedure. Here are my test data:

drop table if exists brand ;
create table brand (
  id int unsigned primary key,
  parent_id int unsigned,
  name varchar(100),
  key (parent_id)
);

insert into brand(id, name, parent_id) values 
  (1, 'aston martin', 0),
  (2, 'vanquish', 1),
  (3, 'V8', 1),
  (85, '10', 2),
  (6, 'bentley', 0),
  (7, 'continental', 6);

brand_level() function take an id as argument and goes up on the hierarchy.

drop function if exists brand_level;
delimiter $$
create function brand_level (brand_id int) returns int deterministic
begin
   declare p_id, l int;
   set l = 0; 
   repeat
      set l = l + 1;
      select parent_id into brand_id from brand where id = brand_id;
   until brand_id = 0 or brand_id is NULL end repeat;
   return if(brand_id=0, l, NULL);
end;
$$
delimiter ;

> select *, brand_level(id) from brand;
+----+-----------+--------------+-----------------+
| id | parent_id | name         | brand_level(id) |
+----+-----------+--------------+-----------------+
|  1 |         0 | aston martin |               1 |
|  2 |         1 | vanquish     |               2 |
|  3 |         1 | V8           |               2 |
|  6 |         0 | bentley      |               1 |
|  7 |         6 | continental  |               2 |
| 85 |         2 | 10           |               3 |
+----+-----------+--------------+-----------------+
6 rows in set (0.00 sec)

If you need to calculate the level often it is better to add a column that contains the level.

alter table brand add (l int unsigned NULL default NULL);

Here is a stored procedure to initialise the brand.l field

drop procedure if  exists add_brand_level;
DELIMITER $$
CREATE PROCEDURE add_brand_level()
BEGIN
    declare rc, level int;
    set level = 1;
    update brand set l = level where parent_id = 0;
    select row_count() into rc;
    while rc != 0 do
        set level = level + 1;
        update brand 
        inner join brand parent on parent.id = brand.parent_id and parent.l is not NULL
        set brand.l = level
        where brand.l is NULL;
        select row_count() into rc;
    end while;
END $$
delimiter ;

Here the result:

> call add_brand_level();
> select * from brand;
+----+-----------+--------------+------+
| id | parent_id | name         | l    |
+----+-----------+--------------+------+
|  1 |         0 | aston martin |    1 |
|  2 |         1 | vanquish     |    2 |
|  3 |         1 | V8           |    2 |
|  6 |         0 | bentley      |    1 |
|  7 |         6 | continental  |    2 |
| 85 |         2 | 10           |    3 |
+----+-----------+--------------+------+
6 rows in set (0.00 sec)
0

I would refer you to exellent Bill Karwin's book 'SQL Antipatterns' https://pragprog.com/book/bksqla/sql-antipatterns . He has a chapter dedicated to implementing tree like structures in SQL databases (he calls them Naive Trees).

Luckily, this chapter is available to download from Pragratic Programmer's website: http://media.pragprog.com/titles/bksqla/trees.pdf but this excerpt doesn't give you the solution to this problem, but Bill Karwin's blog does: http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

The main idea here is to use a separate table Karwin calls a Closure Table. Using the data from another answer here:

drop table if exists brand ;
create table brand (
  id int unsigned primary key,
  name varchar(100)
);

insert into brand(id, name) values 
  (1, 'aston martin'),
  (2, 'vanquish'),
  (3, 'V8'),
  (85, '10'),
  (6, 'bentley'),
  (7, 'continental');

Note that there is no parent_id field here. All the tree node connections are held in another table:

create table brand_closure (
  ancestor int unsigned not null,
  descendant int unsigned not null,
  primary key (ancestor,descendant),
  foreign key fk_ancestor (ancestor) references brand(id),
  foreign key fk_descendant (descendant) references brand(id)
);

This table will hold all connections on all levels between tree nodes:

insert into brand_closure values
  (1,1),(1,2),(1,3),
  (2,2),
  (3,3),
  (6,6),(6,7),(6,85),
  (7,7),(7,85),
  (85,85);

With this set up you can easily query the tree, for the expense of more work when modyfying the table. To get node and it's level:

select b.name, b.id, count(*) as level
  from brand_closure a
  join brand b on b.id = a.descendant
  group by b.name;

+--------------+----+-------+
| name         | id | level |
+--------------+----+-------+
| aston martin |  1 |     1 |
| bentley      |  6 |     1 |
| continental  |  7 |     2 |
| V8           |  3 |     2 |
| vanquish     |  2 |     2 |
| 10           | 85 |     3 |
+--------------+----+-------+

Not the answer you're looking for? Browse other questions tagged or ask your own question.