CODEWITHSUNDEEP
javasqlsap-commerce-cloud
Evgenii
Evgenii

Reputation: 447

Flexible Search query to select entity based on dependent

I have User with Orders. I want to select all users who:
1. Don't have orders at all 2. Do not have orders greater than x date (so if user have 10 orders, and one of them lesser than passed date - I still need this user.
3. Add more conditions to result

I've done this, but it's not working the way I need. First I join all orders with LEFT join, then check - if order greater than passed date - I do not pick an item. Last "or" check I already confused myself, not sure what it's doing (we can't have order without user)

select {c.pk}
from {User as c
left join Order as ord on {c.pk}={ord.user}}
where not exists
({{
  select {o.date} from {Order as o} where {o.date} >='24-Oct-2017'
}})
or {ord.user} is null

Upvotes: 1

Views: 4166

Answers (1)

Johannes von Zmuda
Johannes von Zmuda

Reputation: 1822

In your SubQuery the user check is missing. Add {o.user} = {c.pk} to it.

Upvotes: 2

Related Questions