SQL self join with condition

Question

I have table like:

bu_id   bu_name     parent_id 
1       DefaultBU   NULL
601     bu1         1
602     bu2         601
603     bu3         NULL
604     bu4         603

I want to retrieve the record which bu_id is 1 and the records which has parent_id as selected records bu_id, like:

for 1 bu_id:

I need resuts like:

bu_id   bu_name     parent_id
1       DefaultBU   NULL
601     bu1         1
602     bu2         601

I have tried with:

SELECT b.bu_id, b.bu_name, b.parent_id
FROM `business_unit` AS a
INNER JOIN `business_unit` AS b
WHERE (
b.parent_id = a.bu_id
)

Resuts:

bu_id   bu_name     parent_id
601     bu1         1
602     bu2         601
604     bu4         603

score 2 · Accepted Answer · answered May 19 '15 at 21:06

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following:

set @@session.max_sp_recursion_depth=255;

delimiter $$

create procedure get_bu_tree (
   in parent int
)
begin
    create table if not exists temp_bu_tree_ids (id int);
    set @i = (select count(1) from temp_bu_tree_ids);
    if @i = 0 then insert into temp_bu_tree_ids values (parent); set @i = 1; end if;

    insert into temp_bu_tree_ids (id)
    select bu_id
    from bu
    inner join temp_bu_tree_ids as t
    on bu.parent_id = t.id
    where bu_id not in (select id from temp_bu_tree_ids);

    if (select count(1) from temp_bu_tree_ids) > @i 
    then 
        call get_bu_tree ((select bu_id from bu where parent_id = parent)); 
    else 
        select bu.* 
        from bu
        inner join temp_bu_tree_ids as t
        on bu.bu_id = t.id;

        drop table temp_bu_tree_ids;
    end if;
end$$

delimiter ;

call get_bu_tree (1);

drop procedure get_bu_tree;

Note the call get_bu_tree (1); where 1 is the parent id for which you want to retrieve the children. Also note that this creates and drops a table (temp_bu_tree_ids), check for conflicts!

score 1 · Answer 2 · edited May 23 '17 at 12:40

This is a very common question, and unfortunately the answer is that we've fallen into one of the most serious deficiencies that MySQL has.

In MSSQL, we could use recursive CTEs to accomplish this type of result. Just about every other RDBMS has some implementation that will allow for recursive querying.

Unfortunately, in order to accomplish this cleanly in MySQL, you need to use a stored procedure- examples of which can be found in answers here and here.

jynus · Answer 3 · 2015-05-19T21:48:41.740

Most people just can say that cannot be done in MySQL's dialect of SQL (and they are right), but they do not propose an alternative.

If you cannot change the database structure, use @MatthewSwain option. If you can, however, transform it into the following structure (which you will only have to do once) it can simplify the query a lot, specially if you have many items:

mysql> SELECT * FROM business_unit;
+-------+-----------+
| bu_id | bu_name   |
+-------+-----------+
| 1     | DefaultBU |
| 11    | bu1       |
| 111   | bu2       |
| 2     | bu3       |
| 21    | bu4       |
+-------+-----------+
5 rows in set (0.00 sec)

...the search query will simplify a lot (and it will be faster, as it can use an index):

mysql> SELECT bu_id, bu_name, 
              IF( char_length(bu_id) = 1, NULL, LEFT(bu_id, char_length(bu_id) - 1)) as parent_id 
       FROM business_unit 
       WHERE bu_id like '1%'; -- this is the id to be searched
+-------+-----------+-----------+
| bu_id | bu_name   | parent_id |
+-------+-----------+-----------+
| 1     | DefaultBU | NULL      |
| 11    | bu1       | 1         |
| 111   | bu2       | 11        |
+-------+-----------+-----------+
3 rows in set (0.00 sec)

Try to store the data in the way that will be more efficient for the queries you are going to perform.

For a more in depth explanation, please have a look at this presentation by Bill Karwin: Models for hierarchical data (this is path enumeration).

SQL self join with condition

3 Answers3