mysqlsap-commerce-cloudflexible-search

How to write Flexible Search for joined tables in Hybris

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

Solution

  • You can try something like this :

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