> I wanted to achieve the result without creating a familytree table
> since I am not having the permission to create an extra table
Let's look at the sketchy spec you provided ...
Quote
dealer_id
url
dlr_type
fk_parent_dealerid
1. "dlr_type" can contain the types like "GROUP", "DEALER".
2. For the Main Group there can be any number of sub-groups with the same dlr_type "GROUP"
3. For the dlr_type "GROUP" the fk_parent_dealerid is always NULL for the top most "GROUP" (for the main Group) in the hierarchy.
Either this table is a proper tree---ie it has one row where fk_parent_dealerid is null (the root of the tree) and no dealer_id has more than one fk_parent_dealerid---or it isn't.
If it isn't a proper tree, but has multiple proper trees that you propose to query one at a time, then a query that scopes on the root dealer_id of the desired tree will return a proper tree, so you're ok; otherwise, to get a hierarchical report you will have to write a table that is a proper tree.
If your table is a tree, Listing 7a will efficiently produce a hierarchical report, but it needs to write a working `subtree` table to do so. Listing 7 (just above it in the chapter) implements the same logic, except that table & column names are hardwired, so an alternative for you would be to write your own version of listing 7.
Another alternative is to write a Nested Sets version of your tree (see later in the chapter), but that'll also require writing a table.
A recursive algorithm that doesn't require table writing is in listing 7b: it's about 100 times slower than listings 7 and 7a, perhaps 200 times slower (depending on the tree) than a Nested Sets algorithm.
So, most possible solutions require writing a table, but your employer demands a hierarchical report that writes no tables? When an employer so stupidly ties your hands, you can try to persuade her otherwise, or failing that use a slower algorithm like the one in listing 7b, or find a smarter employer.
> Even if I get a permission to create a familytree table, how I can map the family tree for all those records?
If your table isn't a proper tree, you have to write a new table anyway. Then querying it efficiently requires another table.
Three ways at least to get your results:
1. Use genericTree() as is:
set @tbl = 'yourtablename';
set @edgecol = 'dealer_id';
set @parentcol = 'fk_parent_dealerid';
set @dealer_root = 102863;
call genericTree( @tbl, @edgecol, @parentcol, @dealer_root );
select s.dealer_id, s.fk_parent_dealerid, t.url, t.group
from subtree
join `yourtablename` using(dealer_id);
2. Write your own version of the listing 7 sproc, using the names of your table & columns, so everythjing is in the result table, no need to write your own reporting query after it runs.
3. Write a Nested Sets tree from your table, and use queries from that section of the chapter.