Self Referencing query 3

[SQLScholar]

hey all,

I have two tables which look something like this:

[Category]
ID
Name
FK_catID

[Items]
ID
Name
FK_catID

So basically it builds a heirarchy. An Item is a member of a category and a category can be a member of a category.

So lets sugguest we are talking about Samsung E75 Laptop.

It would be in itself an item. The item would be in the category laptops. Laptops would be in the category electronics, and electronics would be in a category called things. Things would not have a fk_catID as it will be the top level. So we have

Things>
Electronics>
Laptops>
Samsung E75

Obviously there will be other things in each catergory, including other categoies or items. So under electronics may have Televisons for instance.

Now.... what i have is an issue. I think the table design is correct, but now i have been asked. If i have an Item id, i need to return the top most level that its a part of.

How can i do this?

Any help or guidance much appreciated.

Dan


----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[r937]

all you need is a single query with as many LEFT OUTER JOINs as you have levels of subcategories

examples of this type of query are here: Categories and Subcategories

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SQLScholar]

r937,

Surely that cant be the only way to do it.... so every time you increase the depth you have to change the query?

I am quite new to Mysql (used to MS SQL) can you do loops like you can in transact? Would that be a better option or would that be too resource intensive?

Thanks for your advice?

Dan



----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[r937]

what is the maximum number of levels that is present in your hierarchy?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SQLScholar]

currently 5-6 but it could get to around 9-10 i am guessing....

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[r937]

9 to 10 is still quite manageable

use a query with 10 LEFT OUTER JOINs and then use COALESCE to find the last node "going up the tree" that is not null

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Spork52]

You could create a loop of joins which would execute until no results were obtained.

Something like this (I'm sure I screwed up the syntax here, but I just wanted to outline the idea):

$item_query = "SELECT FK_catID FROM items where items.ID = '".$item_id."'";
$result = mysql_query($item_query) or die(mysql_error());
while ($row = mysql_fetch_array($result))
		{
		$new_FK_catID = $row['FK_catID'];
		}
do
{
$old_FK_catID = $new_FK_catID
$category_query = "SELECT  category.FK_catID from category WHERE  category.ID = '".$new_FK_catID."'";
$result = mysql_query($category_query) or die(mysql_error());
while ($row = mysql_fetch_array($result))
		{
		$new_FK_catID = $row['FK_catID'];
		}
}
while (insert code to check if $new_FK_catID is an ID in the category table)

When the do while loop exits $old_FK_catID will be the category ID of the top level.

Above assumes categories and items do not belong to multiple categories. You might be able to use arrays and more generalized looping to overcome this issue.

 

[Spork52]

This might be useful:

http://www.mysqltutorial.org/stored-procedures-loop.aspx

 

[ingresman]

Have a look at

http://articles.sitepoint.com/article/hierarchical-data-database

 

[SQLScholar]

thanks guys - think i am going to do a stored proc to backfill a new column as this query would need to run too often.

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------