How do I get all rows from ITEM table, which are children of a parent ITEM table row, where relationship is stored separately?

Question

How do I get all rows from ITEM table, which are children of a parent ITEM table row, where relationship is stored separately? How can I do a join to do this? "get all rows from ITEM table, which are children of this specific ITEM table row, all child items from this parent item, where relationship is stored in separate RELATIONSHIP table"

So given there is an ITEMS and a RELATIONSHIPS table. The key columns are:

ITEMS
* ID
* << other columns>>

RELATIONSHIPS
* PARENT_ID
* CHILD_ID

I'm trying to understand whether the DataSet / DataRelation approach could somehow map these relations. For example if I basically want a way to implement the request "Give me all children ITEMS in a DataRow[] form, given a parent ITEM DataRow, based on the RELATIONSHIPS table", is there a way to do this using a DataRelation? Of if not what would be the easiest way to do this using the DataSet approach?

EDIT: That is, assuming I am using a DataSet, and within the DataSet I have one DataTable for each of the physical database tables I described above.

Thanks

Answer 1

Top of my head, you're looking for roughly this solution (and I'm not entirely certain if I understand your datastructure correctly):

SELECT child.othercolumns
FROM items AS child, relationships AS r, items AS parent
WHERE r.parent_id=parent.id AND r.child_id=child.id