[RESOLVED] SQL query - can this be done without a loop?

[Atheist]

Hey. Sorry for the dumb thread title - I'm so confused about this that I cant even name my problem.

I have two tables:
assets

• id - primary key
• name - varchar(255)
• ...

asset_dependencies

• parent_id
• child_id

asset_dependencies contains relationship between assets, where the asset referred to by child_id "depends" upon the asset referred to by parent_id.

What I'm struggling with:
I'm trying to write a query where, given the name of an asset, it returns all assets upon which the asset depends (both directly and indirectly).
This could be visualized by a tree structure:


Querying for the dependencies of "Asset A" would return "Asset B", "Asset C" and "Asset D". Finding out that Asset B and C should be included in the result is trivial, as they are directly connected to Asset A in the asset_dependencies table. However, from there I must find that Asset D should be included in the result aswell, because Asset C depends upon it.
As you see this could become a dependency chain of indefinite size.
I hope my problem description make sense. I just can't figure out how to write my query.

Edit: I should also mention I'm using sqlite.

[leinad31]

What you need is support for hierarchical queries, unfortunately it is not supported by sqlite.

Either you change your database to one that supports hierarchical queries, or build the tree in at the front-end or mid-tier (memory expensive since you have to store tree somewhere, iterative processing with multiple SQL calls to retrieve child rows, error handling for cyclic redundancy required).

[Atheist]

Thanks, it was aggravating not getting it to work, but it feels alot better to know that it isn't possible.

I worked around the problem the way you mentioned, I query the assets for their dependencies one at a time, creating a tree structure in memory. I suppose its no big deal doing it this way!

Thanks again!

[leinad31]

Just don't forget to handle cyclic redundancy (root/ancestor is also child)