How do I retrieve items belonging to sub categories by selecting a top level category?

Please see the data tables and query below ..

Items
Id, Name
1, Item 1
2, Item 2

Categories
Id, Name, Parent ID
1, Furniture , 0
2, Tables, 1
3, Beds, 1
4, Dining Table, 2
5, Bar Table, 2
4, Electronics, 0
5, Home, 4
6, Outdoors, 4
7, Table la开发者_开发技巧mp, 4

ItemCategory
ItemId, CategoryId
1, 2 .. Row1
2, 4 .. Row 2
2, 5 .. Row 3 

ItemCategory table stores which items belongs to which category. An item can belong to top level and or sub category. there are about 3 level deep categories, that is, Tob level, sub level, and sub sub level.

Users select all of the categories they want to view and submit and I can query the database by using a sample query below..

    SELECT * FROM items i INNER JOIN ItemCategory ic ON 
ic.itemId = i.itemId AND ic.itemId IN ('comma separated category ids')

This works fine.

My question is that Is it possible to view all the items under a top level category even though it has not been directly assigned to the item. For example, if users select Furniture above, then it lists all the items belonging to its sub categories (even though the ItemCategory doesn't contain any record for it)??

I'm open to making necessary amendements to the data table or queries, please suggest a solution. Thank you.

Watcher has given a good answer, but I'd alter my approach somewhat to the following, so you have a structured recursive 2-dimensional array with categories as keys and items as values. This makes it very easy to print back to the user when responding to their search requirements.

Here is my approach, which I have tested:

$items = getItemsByCategory($topCategory);
//To print contents
print_r($items);

function getItemsByCategory($sid = 0) {
  $list = array();
  $sql = "SELECT Id, Name FROM Categories WHERE ParentId = $sid";
  $rs = mysql_query($sql);
  while ($obj = mysql_fetch_object($rs)) {
    //echo $obj->id .", ".$parent." >> ".$obj->name."<br/>";
    $list[$obj->name] = getItems($obj->id);
    if (hasChildren($obj->id)) {
        array_push($list[$obj->name],getItemsByCategory($obj->id));
    }
  }
  return $list;
}

function getItems($cid) {
  $list = array();
  $sql = "SELECT i.Id, i.Name FROM Items p INNER JOIN ItemCategory ic ON i.id = ic.ItemId WHERE ic.CategoryId = $cid";
  $rs = mysql_query($sql);
  while ($obj = mysql_fetch_object($rs)) {
    $list[] = array($obj->id, $obj->name);
  }
  return $list;
}

function hasChildren($pid) {
  $sql = "SELECT * FROM Categories WHERE ParentId = $pid";
  $rs = mysql_query($sql);
  if (mysql_num_rows($rs) > 0) {
    return true;
  } else {
    return false;
  }
}

Hope this helps.

With recursion, anything is possible:

function fetchItemsByCat($cat, &$results) {
    $itemsInCat = query("SELECT Items.Id FROM Items INNER JOIN ItemCategory ON ItemCategory.ItemId = Items.Id WHERE CategoryId = ?", array($cat));

    while($row = *_fetch_array($itemsInCat)) 
        array_push($results, $row['Id']);

    $subCategories = query("SELECT Id FROM Categories WHERE Parent = ?", array( $cat ));
    while($row = *_fetch_array($subCategories))
        $results = fetchItemsByCat($row['Id'], $results);

    return $results;
}

$startCat = 1;  // Furniture

$itemsInCat = fetchItemsByCat($startCat, array());

The function is somewhat pseudo-code. Replace *_fetch_array with whatever Database extension you are using. The query function is however you are querying your database.

Also, this is untested, so you should test for unexpected results due to using an array reference, although I think it's good to go.

After calling the function, $itemsInCat will be an array of integer ids of all of the items/subitems that exist in the given start category. If you wanted to get fancy, you can instead return an array of arrays with each 2nd level array element having an item id as well as that item's assigned category id, item name, etc.

If you use MySQL, you're out of luck short of indexing your tree using typical techniques, which usually means pre-calculating and storing the paths, or using nested sets:

http://en.wikipedia.org/wiki/Nested_set_model

If you can switch to PostgreSQL, you can alternatively use a recursive query:

http://www.postgresql.org/docs/9.0/static/queries-with.html

Evidently, you can also recursively query from your app, but it's a lot less efficient.