SQL self join with condition 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
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) &gt; @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!