CODEWITHSUNDEEP
mysqlsql
Sara
Sara

Reputation: 489

MySQL - While in SELECT clause

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.

Upvotes: 1

Views: 8233

Answers (2)

Shaharyar
Shaharyar

Reputation: 12439

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

Upvotes: 1

Webster
Webster

Reputation: 1153

just use CASE

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

Upvotes: 1

Related Questions