views:

135

answers:

1

Hi,

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

A: 

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
MSalters
this would be the SQL I assume? That is, I meant my question to be how to setup the DataSet (with a DataTable within for each physical database table, and some DataRelations) to then be able to carry out the task within the DataSet world?
Greg
Or perhaps you were suggesting rather than using DataSet/DataRelations it's easier to just populate a specific DataTable just with the required rows using the SQL you suggested?
Greg
With `DataSet`, did you mean the .NET class ? Your question really left out relevant details.
MSalters
Sorry-yes. An ADO.Net question effectively
Greg
Ok, tagged your question.
MSalters