SELECT DISTINCT in DataView's RowFilter

Question

I'm trying to narrow down the rows that are in my DataView based on a relation with another table, and the RowFilter I'm using is as follows;

dv = new DataView(myDS.myTable,
        "id IN (SELECT DISTINCT parentID FROM myOtherTable)",
        "name asc",
        DataViewRowState.CurrentRows);

"myTable" and "myOther" table are related via myTable.ID and myOtherTable.parentID, and so the idea is that the DataView should only contain rows from "myTable" which have corresponding child rows in "myOtherTable".

Unfortunately, I'm getting this error;

Syntax error: Missing operand after 'DISTINCT' operator.

The SQL is fine as far as I am aware, so I'm wondering is there some limitation on using the DISTINCT keyword as part of RowFilter's SQL? Anyone have any idea?

Answer 1

Try just leaving out the "DISTINCT". In this case, the results should be the same with or without. Troubleshoot from there.

Answer 2

Unfortunately, I don't think you can't perform a subquery in a DataView's filter expression. You are only allowed to use a subset of SQL in these expressions (documented here).

You'll probably need to perform your subquery (SELECT DISTINCT parentID FROM myOtherTable) separately.

This article describes the problem and a possible solution.

Answer 3

This question will show you how to create a distinct set (actually a new datatable) using a DataView on a table. The approach is slightly different than what you're doing above.