[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.