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

Unfortunately, I don't think you can perform a subquery in a DataView's filter expression. You're only allowed to use a subset of SQL in some 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.