I'm trying to construct a subquery and head's spinning (there doesn't appear to be a smiley for that! ).

I'm trying to do something like:

select foo.somefield, bar.someotherfield
from foo
left join bar on bar.foreignkey = foo.primarykey
where bar.yetanotherfield = "something"
and exists
(select *
from bar
where bar.foreignkey = foo.primarykey
and bar.anotherfield  "blah")

I've seen in other posts that you recommend reformulating this as an outer join and testing for null for simplicity, but I don't think that's going to work in this case: I need to ensure that there is at least one child bar record that has anotherfield="blah", then all joined bar records are valid.

I'm using Adapter on Oracle.

You can use a FieldCompareSetPredicate for this, using the Exists setoperator. To make it a co-related subquery, you could alias Foo in the outer query's relation and specify that alias as well with the foo filter in the subquery.

What you you tried yourself which failed? Perhaps it's a small thing that's wrong.

Otis wrote:

What have you tried yourself which failed? Perhaps it's a small thing that's wrong.

So far I have:

                 filterBucket.PredicateExpression.Add(New FieldCompareSetPredicate( _
                    Nothing, Nothing, _
                    EntityFieldFactory.Create(ActivityFieldIndex.CdfId), Nothing, _
                    SetOperator.Exist, _
                    PredicateFactory.CompareExpression(ActivityFieldIndex.CdfId, _
                        ComparisonOperator.Equal, _
                        New Expression(EntityFieldFactory.Create(CdfLineFieldIndex.Id))), _
                    True))

Which takes care of:

 and not exists 
(select activity.cdfid
from activity
where activity.cdfid = cdf.cdfid)

how can I add an additional 'and' to the subquery to make it:

 and not exists 
(select activity.cdfid
from activity
where activity.cdfid = cdf.cdfid
and activity.actdate is null)

The FieldCompareSetPredicate will only accept IPredicate not IPredicateExpression

FieldCompareSetPredicate

Accepts IPredicateExpression also

I tried and it compiled and runned with the right results.

Please use your IPredicateExpression instead of the IPredicate.

Thanks Walaa. IPredicateExpression derives from IPredicate

For anyone looking for an example here's what I ended up with:

First construct the where portion of the subquery:

                 Dim filter As IPredicateExpression = New PredicateExpression
                filter.Add(PredicateFactory.CompareNull(ActivityFieldIndex.ActualDate))
                filter.Add(PredicateFactory.CompareExpression(ActivityFieldIndex.CdfId, _
                        ComparisonOperator.Equal, _
                        New Expression(EntityFieldFactory.Create(CdfLineFieldIndex.Id))))

Then add that to the FieldCompareSetPredicate:

                filterBucket.PredicateExpression.Add(New FieldCompareSetPredicate( _
                    Nothing, Nothing, _
                    EntityFieldFactory.Create(ActivityFieldIndex.CdfId), Nothing, _
                    SetOperator.Exist, _
                    filter))

In my example activity is what I'm checking the existence of (the subquery) which is being correlated to cdf outside of the query