1

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

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Pertsh Galstyan
  • 41
  • 7
  • Please would you read, [Tips for asking a good Structured Query Language (SQL) question)](https://meta.stackoverflow.com/questions/271055) and amend your question accordingly. – RiggsFolly Oct 18 '22 at 15:17
  • FYI, the issue with your query is you check if the owner of the user is null. but you want that to be the address. so the where should be `{a.owner} IS NULL` – Yoni Oct 22 '22 at 19:37

2 Answers2

1

You can try something like this :

SELECT {pk} FROM {User} WHERE {pk} not in ({{ SELECT {owner} FROM {Address} WHERE {owner} IS NOT NULL }})
Benkerroum Mohamed
  • 1,867
  • 3
  • 13
  • 19
0

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

Roman Lototskyi
  • 692
  • 1
  • 5
  • 13