How to write Flexible Search for joined tables in Hybris

Question

I am trying to get all users which don't have addresses, there is a relation between user and address tables(one to many) and the address has an owner attribute that refers to Users pk. how can I achieve that ??

SELECT {u:pk} 
FROM {User AS u 
    LEFT JOIN Address AS a ON {u:pk}={a:owner}} 
WHERE {u:owner} IS NULL

I wrote a query which I guess doesn't work correctly

Answer 1

You can try something like this :

SELECT {pk} FROM {User} WHERE {pk} not in ({{ SELECT {owner} FROM {Address} WHERE {owner} IS NOT NULL }}) 

Answer 2

You should write {a:owner} not {u:owner}. This query gave me the same result:
SELECT {u:pk} FROM {User AS u LEFT JOIN Address AS a ON {u:pk}={a:owner}} WHERE {a:owner} IS NULL