Author Topic: Non-Existence of Relation via SQL Query  (Read 251 times)

Daiim

  • EA User
  • **
  • Posts: 40
  • Karma: +0/-0
    • View Profile
Non-Existence of Relation via SQL Query
« on: May 22, 2022, 08:41:51 pm »
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:
Code: [Select]
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:
Code: [Select]
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?
« Last Edit: May 22, 2022, 09:17:22 pm by Daiim »

qwerty

  • EA Guru
  • *****
  • Posts: 12778
  • Karma: +363/-295
  • I'm no guru at all
    • View Profile
Re: Non-Existence of Relation via SQL Query
« Reply #1 on: May 22, 2022, 08:45:23 pm »
How do you pass that to EA (query builder, API) and what database are you utilizing?

q.

Daiim

  • EA User
  • **
  • Posts: 40
  • Karma: +0/-0
    • View Profile
Re: Non-Existence of Relation via SQL Query
« Reply #2 on: May 22, 2022, 09:07:19 pm »
I always try this in a <<Model View>> first, and then I use this in code via API (Repository.SQLQuery) with JScript. In this case, the EA Editor of the Model View already complains with the syntax error. I'm using an local EAP file (DB Version 4.01).

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11824
  • Karma: +458/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Non-Existence of Relation via SQL Query
« Reply #3 on: May 22, 2022, 09:57:47 pm »
I've never heard of the Except function.

What I use in those cases is

Code: [Select]
Where not exist
  (select <whatever you don't want to be included>)

This works in all databases I know of.

Geert