Queries within queries

[scottybwoy]

Is it possible to have a query within a query?  Let me expand...

 

I have a table of categories and a table of products that belong to the categories.  In the table of categories there may be child categories.  Usually the parent category doesn't have products in it, but just child categories within the same table.  The structure is like this to keep it simple I will just show the relevant fields :

| categories_id | parent_id |
=====================
| 29                |              |
| 30                | 29          |
| 31                | 30          |
| 32                | 30          |
...

And the other table is just as simple :

| product_id | category_id |
=====================
| 21            | 31             |
| 22            | 32             |
| 23            | 31             |
...

So is it possible to have a single query that can take a category_id, lets say 29 in this example, discover whether it is a parent, if so does its child have a daughter (this loop recursive until the category has no children) Then retrieve all the product_ids for it?

 

Hope that makes sense?

 

Thanks

[scottybwoy]

Bump, is it too complex? Not possible? How do I go about finding out, any good resources?

[Aimee]

Can you not do a recursive function instead?  I imagine it would be more flexible.  So in my example I'm just returning a list of IDs but you get the point..

 

function getChildren(parentID)

{

$sql = "SELECT id FROM categories WHERE parentID='$parentID'";

// loop through results and for each one call:

$allChildren .= "$id,";

$allChildren .= getChildren($id);

return $allChildren;

}

 

$allChildren = getChildren("");

[scottybwoy]

Yes, that is what I have set up so far, but as it gets bigger there would be calls to the database and I thought it would be much quicker if it could be done in a single MySQL query.

 

Thanks for the reply tho.

[aschk]

You can do it in 1 statement but it requires an alteration to your table layout.

The other option is to perform some LEFT JOINs although i haven't fully considered this option yet regarding sister categories...

[scottybwoy]

OK so there is no fandagaley recursive MySQL functions that could do it?

[fenway]

No, not without a stored procedure...