It might look like the structure below:
from Module.Table1 as t1
left outer join t1/Module.Table1_table2/Module.Table2 as t2
where t2/ID is null
and t1/valid = 'false'
select t1/Sitename as Sitename,
t1/SiteAddress as address
The OQL error message when trying to execute the query.
For OQL try something like this:
SELECT site/SiteName
FROM Portal.Site AS site
WHERE
site/Active = 0
AND site/id NOT IN (
SELECT site2/id
FROM Portal.Site AS site2
INNER JOIN site2/SiraFieldToolkit.SiteInvestigation_Site/SiraFieldToolkit.SiteInvestigation AS siteinv
)
The subquery lists all id’s from sites with an associated siteinvestigation. If it’s not in the list, it has no assocation.
Use left outer join with a where clause excluding results that do have an association with taleB
TableA LEFT OUTER JOIN TableB ON idfieldA=idfieldB
WHERE (<id-attribute of tableB>) is null
This will get you all records of tableA without an association to tableB
See https://docs.mendix.com/refguide/oql-left-outer-join for more info
I may be understanding you wrong but for a not statement you should use: not(association/entity) instead of not[association/entity]
for example: [ Active = false() and not(Order_Price/Price) ]