Easy query?

[bofett]

Please help.

Table (cats)  has id, name,  parent.

I am trying to echo the name of the parent when I know the id of the child.  So for example…

id  name          parent

1  Automotive  NULL

2  Cleaning          1

3  Repair           1

 

I would like to display “ Automotive – Cleaning”  When I know id=2.  What is the best way to query for “Automotive”?

Thanks

 

[fenway]

You'll have to "join" all the way up the chain -- which is difficult without a loop, SPs, or pre-existing knowledge.  There's a sticky on hierarchical data.

[kkubek]

I'm too lazy to create the table to test this, but does this work ?

 

select b.name, a.name

from cats a, cats b

where a.parent = b.id

and a.id =2

[aschk]

Google "Joe Celko's nested sets" for your answer.

[bofett]

This did the trick...

 

	function getCat($cat){
	$qr1 = mysql_query("SELECT name FROM cats WHERE id='$cat'");
	$a = mysql_fetch_object($qr1);
	return $a->name;
}
function getsubcat($cat){
	$qr1 = mysql_query("SELECT parent FROM cats WHERE id='$cat'");
	$a = mysql_fetch_object($qr1);

	if ($a->parent != "") {
	$qr2 = mysql_query("SELECT name FROM cats WHERE id=$a->parent");
	$a2 = mysql_fetch_object($qr2);
	echo "{$a2->name} - ";
 }else{
        }
  }

[Ken2k7]

Which is a very long redundant version of:

 

SELECT c1.name
FROM cats c1
INNER JOIN cats c2
     ON c1.id = c2.parent
WHERE c1.id = $id

 

right?

 

Edit: my sql should be simplified but for some reason, I can't think right now.