OQL Query for retrieving a constrained list

0
Hi! Does anyone know what the OQL statement would be to query something like:- Retrieving an Entity with an XPath constaint to show all Inactive Sites (Active = False) and no associated data [not[enter association] I keep getting an error when trying to see if the association is empty so assume it is something to do with how I have written it. Apologies, I am not too familiar with OQL. Thanks!
asked
5 answers
1

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
 

answered
0

The OQL error message when trying to execute the query.

answered
0

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.

answered
0

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

answered
-1

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) ]

answered