Category and Sub Category

[dweb]

Hi everyone

 

I'm trying to sort out this SQL code so that is displays as I need it.

 

The table I have looks like;

 

id     name     parent
1     HP           0
2     APPLE     0
3     Printer    1
4     Mouse    1
5     iPod       2
6     iPad        2

 

and I need to output the QUERY as

 

id   value     sub_item    parent
2   Apple      0                0
6   iPad        1                2
5   iPod        1                2
1   HP          0                0
4   Mouse    1                1
3   Printer    1                1

 

So it basically displays the sub items under the main items

 

The code I am using is

select root.name  as root_name
     , sub.name as sub_name
  from comments as root
left outer
  join comments as sub
    on sub.parent = root.id

where root.parent = 0 AND sub.parent != 0
order 
    by root_name 
     , sub_name

 

 

but I can't seem to get it to work as my example shows

 

any ideas??

[dweb]

Someone suggested I use Recursive CTE but has not explained this very well, any suggestions on if I should and if so, how I can use Recursive CTE to get my result

[fenway]

What does your query output? How deep in your nesting?

[dweb]

Hi

 

It gives me

 

root_name 	sub_name
Apple 	        iPad
Apple 	        iPod
HP 	               Mouse
HP 	               Printer

 

the problem is that it ignores any record which doesn't have a "child", for example I have "Dell" as a main category, and it excludes it.

 

I also wanted to have all results in one column with the parent at the top and child underneath with a type column indicating if it's a child(1) or parent (0), such as

 

root_name     type
Apple             0
iPad               1
iPod               1
HP                  0
Mouse           1
Printer          1
DELL             0

 

unless I can tweak the query to look like

 

root_name 	sub_name
Apple               NULL
NULL                iPad
NULL                iPod
HP                    NULL
NULL                Mouse
NULL                Printer

DELL                NULL

 

which I guess would do the same trick

 

my table is going to be huge, around 800,000 records, so i'm trying to make it as quick as possible, so any suggestions would be great

[dweb]

Someone suggested starting the code off with

 

SELECT Concat(If(isnull(p2.id),"",Concat("/",p2.id)),"/",p1.id) AS `generated path`

 

and then ending it with

 

ORDER BY `generated path`

 

so it generated something like

 

/1

/1/3

/1/4

/2

/2/5

/6

 

but would this be the best and fastest way as it seems to work?

[Barand]

My tablename and col names slightly different but here is a recursive solution

 

$sql = "SELECT c.category_id, c.parent, c.name FROM category c";
$res = mysql_query($sql) or die(mysql_error());
$cats = array();
while (list($id,$parent,$cat)=mysql_fetch_row($res)) {
    $cats[$parent][$id] = $cat;
}


function subcats(&$cats, $pid, $level=0)   {
    if (isset($cats[$pid])) {
        foreach ($cats[$pid] as $id=>$cat) {
            $sub = $level>0 ? 1: 0;;
            printf ('%-20s %5d %5d<br />', $cat,$sub,$pid);
            subcats($cats,$id, $level+1);
        }
    }

}

echo '<pre>';
subcats ($cats,0);
echo '</pre>';

[fenway]

If it's just a single sub-category, a single LEFT JOIN should be sufficient.

[Barand]

What does your query output? How deep in your nesting?

 

As the OP never answered your question, we don't know how deep