MySQL - While in SELECT clause

Question

Is it possible to call a while statement inside a SELECT clause in MySQL ?

Here is a example of what I want to do :

CREATE TABLE `item` (
  `id` int,
  `parentId` int,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `FK_parentId` (`parentId`),
  CONSTRAINT `FK_parentId` FOREIGN KEY (`parentId`) REFERENCES `item` (`id`)
);

I would like to select the root of each item, i.e. the higher ancestor (the item that has no parentId). In my mind, I would do something like this :

select 
    `id` as 'ID',
    while `parentId` is not null do `id` = `parentId` end while as 'Root ID'
from
    `item`

Of course this can't work. What is the better way to achieve something like that ?

EDIT

Here a sample data :

id  |   parentId
1   |   NULL
2   |   1
3   |   2
4   |   2
5   |   3
6   |   NULL
7   |   6
8   |   7
9   |   7

And expected result :

ID  |   RootId
1   |   NULL
2   |   1
3   |   1
4   |   1
5   |   1
6   |   NULL
7   |   6
8   |   6
9   |   6

Thank you.

`while` is not part of the SQL query language. You cannot really do what you want in a `select` using MySQL because it has no real support for hierarchical data structures or recursive queries. — Gordon Linoff, Jan 14 '16 at 13:33
It's not clear what you're trying to do. If you just want records with no `parentId` then wouldn't that just be `WHERE `parentId` IS NULL`? — David, Jan 14 '16 at 13:34
What @GordonLinoff said, more information about hierarchical structures in relational databases can be found here: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database?lq=1 — Martin Schneider, Jan 14 '16 at 13:37

score 1 · Answer 1 · answered Jan 14 '16 at 13:34

1

just use CASE

select 
    `id` as 'ID',
    CASE `parentId` WHEN is not null THEN `parentId` END as 'Root ID'
from
    `item`

answered Jan 14 '16 at 13:34

Webster

1,113
2
19
39

Thank you for your answer, but that's not what I want to do. I did not properly explain my problem so I added an example of what I need. – Sara Jan 14 '16 at 13:44

score 1 · Answer 2 · edited May 23 '17 at 11:59

Here is the procedure:

BEGIN

    -- declare variables
    DECLARE cursor_ID INT;
    DECLARE cursor_PARENTID INT;
    DECLARE done BOOLEAN DEFAULT FALSE;

    -- declare cursor
    DECLARE cursor_item CURSOR FOR SELECT id, parentId FROM item;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- create a temporary table
    create TEMPORARY  table IF NOT EXISTS temp_table as (select id, parentId from item);
    truncate table temp_table;

    OPEN cursor_item;
    item_loop: LOOP

    -- fetch row through cursor
    FETCH cursor_item INTO cursor_ID, cursor_PARENTID;
        IF done THEN
            -- end loop if cursor is empty
            LEAVE item_loop;
        END IF;
            -- insert into 
            insert into temp_table
            select MAX(t.id) id, MIN(@pv := t.parentId) parentId
            from (select * from item order by id desc) t
            join (select @pv := cursor_ID) tmp
            where t.id = @pv;
    END LOOP;

    -- close cursor
    CLOSE cursor_item;

    -- get the results
    SELECT id id, parentid RootId from temp_table order by id ASC;
END

I created a temporary table and kept the results into it while running cursor. I couldn't think of a solution with just one query. I had to go for a cursor.

I took help from the following links:

How to do the Recursive SELECT query in MySQL?

How to create a MySQL hierarchical recursive query

Thank you for your answer, but that's not what I want to do. I did not properly explain my problem so I added an example of what I need. — Sara, Jan 14 '16 at 13:44
I know, I mistook your question as it wasn't clear. What is the criteria of this result, I can't find out the pattern. As I think in your expected result the `RootId` of `7` `8` `9` should be `6`. Why it is `2`? — Shaharyar, Jan 14 '16 at 13:46
Ah you're right ! I wrote it too fast. Thank you for noticing it. — Sara, Jan 14 '16 at 13:47

MySQL - While in SELECT clause

2 Answers2

Linked