Filtering Parent table on child records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day,

I have a DataSet with 2 tables, Parent & Child, that are connected by a
DataRelation. I would like to filter the Parent table to display only those
records which have a record in the Child table that satisfies a certain
condition.

Any suggestions?

Cheers,

Helen
 
Like Cor mentions, GetChildRows is your best bet. You can also use a
DataView and reset the RowFilter property which is sometimes a little
cleaner syntactically but less performant. Overall I'd use Cor's approach
 
The trouble is that you have to first call GetChildRows() on each parent row,
which I was hoping to avoid.

In my case I think it will be faster to filter the child table, then walk
through the results to get the parent row, building a DataView on them.

I was hoping there was a more SQL-like way, to do it all in one step.

Thanks for your responses.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com


W.G. Ryan said:
Like Cor mentions, GetChildRows is your best bet. You can also use a
DataView and reset the RowFilter property which is sometimes a little
cleaner syntactically but less performant. Overall I'd use Cor's approach
Click to expand...
 
Helen,

There are mostly for profesionals no one step solutions, just because they
don't perform as we wish.
(Don't forget that SQL was created as a simple plain language for endusers).

But there will be Linq in future which should cover your question.

Cor

Helen Warn said:
The trouble is that you have to first call GetChildRows() on each parent
row,
which I was hoping to avoid.

In my case I think it will be faster to filter the child table, then walk
through the results to get the parent row, building a DataView on them.

I was hoping there was a more SQL-like way, to do it all in one step.

Thanks for your responses.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
Click to expand...
 
Hi Cor,

I find from the docs that you can use only aggregate functions on the Child
in the RowFilter property of the parent, and these do not allow a condition.

I was going to make a suggestion that a future version of ADO.NET expand
this functionality, but find that it has already been done. Until then ...

Laters

Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
 
As far as I can see, you run into exactly the same problem. A filter
condition that references a Child column must be an aggregate function, and
the argument to the aggregate function can only be a column name, not an
expression using the column name.

cheers,

Helen
 
I ran into the same problem and because I wondered why an aggregate shouldn't be appropriate for the given probelm, I tried it out successfully.

If I didn't get the problem wrong, that should be one (!) solution:

I have a checkbox "Hide rows with no matches" on a Form with two data grids; a relation exists.

The event handler looks like this:

this.Cursor = Cursors.WaitCursor;
if (cbHideXrows.Checked)
{
string Filter = "Count(Child("
+ _ds.Relations[0].RelationName
+ ")."
+ _ds.Relations[0].ParentColumns[0].ColumnName
+ ") > 0";
dvLeft.RowFilter = Filter;
}
else
{
dvLeft.RowFilter = "";
}
///////////////////////////////////////
dvLeft is the view behind the left DataGrid.
It can be determined with the following code, assuming that the datasource of leftGrid is already set to _ds.Tables[0]
///////////////////////////////////////
CurrencyManager cmLeft = (CurrencyManager)leftGrid.BindingContext[_ds.Tables[0]];

dvLeft = (DataView)cmLeft.List;

///////////////////////////////////////
Any comments appreciated.

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Back
Top