Hi there,
the knowledge of existing relations in a model is as important as the knowledge of relations that are explicitly missing between some object types. I try to figure out how to express this within an SQL Query.
Example 1)
Elements that have a port with a certain tagged-value I can query the following way:
SELECT element.ea_guid AS GUID
FROM ((t_object element
LEFT JOIN t_object port ON (port.ParentID = element.Object_ID))
LEFT JOIN t_objectproperties tag ON (tag.Object_ID = port.Object_ID))
WHERE <element filter criteria> AND <port filter criteria> AND tag.Property = '<TaggedValue-Name>'
Now I try to find a way to query for elements that explicitly do not have these relations. Usually I would create a set A with all elements that have the <element filter criteria> and a set B with elements that have the tagged value relation to finally create the result via A except B.
My expectation would be, that a query using EXCEPT (
https://www.techonthenet.com/sql/except.php) would give me what I want:
SELECT other.ea_guid AS CLASSGUID, other.Name AS Name
FROM t_object other
WHERE <element filter criteria>
EXCEPT
SELECT element.ea_guid AS CLASSGUID, element.Name AS Name
FROM ((t_object element
LEFT JOIN t_object port ON (port.ParentID = element.Object_ID))
LEFT JOIN t_objectproperties tag ON (tag.Object_ID = port.Object_ID))
WHERE <element filter criteria> AND <port filter criteria> AND tag.Property = '<TaggedValue-Name>'
But EA complains about a missing operator (in the first WHERE clause). What am I doing wrong?
