Check if id exists in foreign table twice

Question

After trying for a while I thought I'd try to ask here for a change.
I am trying to check if a person id exists in two tables, for one table it works like a charm, but if I try to check another table I get the following error:

[Semantical Error] line 0, col 268 near 'owner FROM \...\Entity\Resource':
Error: Invalid PathExpression. Must be a StateFieldPathExpression.

The trick is that I can only use one DQL query, and the following is what I came up with (... not in actual query):

SELECT contact_person
FROM \\...\Entity\Person contact_person
WHERE NOT EXISTS (SELECT b.personId FROM \\...\Entity\Booking b WHERE b.personId = contact_person.id)
AND NOT EXISTS (SELECT r.owner FROM \\...\Entity\Resource r WHERE r.owner = contact_person.id)

Answer 1

Your query is not the easiest one. You should simplify it. If you want to select all persons who don't appear in both Booking and Resource tables you should use this query:

SELECT contact_person.*
FROM Person contact_person
LEFT JOIN Booking b  ON contact_person.id = b.personId
LEFT JOIN Resource r ON contact_person.id = r.owner
GROUP BY contact_person.id
HAVING COUNT(r.owner) = 0 AND COUNT(b.personId) = 0

Joins are much faster than subqueries on each row. And this query is more clear.

PS. Sorry I don't know DQL dialect but I think you can modify my query for your needs.