Author Topic: SQL query finding elements without specific child element  (Read 1667 times)

M1TO

  • EA User
  • **
  • Posts: 22
  • Karma: +0/-0
    • View Profile
SQL query finding elements without specific child element
« on: March 17, 2025, 07:42:10 pm »
I have trouble writing the correct SQL query for the following problem: I want to find all elements of a specific type which don't have a specific child element but can have other child elements.
My approach was:
Code: [Select]
SELECT DISTINCT obj.ea_guid AS CLASSGUID, obj.Object_Type AS CLASSTYPE, obj.Name

FROM t_object obj

WHERE NOT EXISTS (
SELECT * FROM t_object obj2 WHERE obj2.ParentID = obj.object_id AND obj2.Object_Type = 'SpecificType')
AND obj.Stereotype = 'Some Stereotype'

I am pretty sure, the solution is simple. So what am I missing?

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13387
  • Karma: +566/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query finding elements without specific child element
« Reply #1 on: March 17, 2025, 08:49:20 pm »
And the problem is?

Geert

M1TO

  • EA User
  • **
  • Posts: 22
  • Karma: +0/-0
    • View Profile
Re: SQL query finding elements without specific child element
« Reply #2 on: March 17, 2025, 09:59:59 pm »
The problem is, that the query does not find the elements with the missing child element. It lists all the elements, regardless whether the child element is existing or not.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13387
  • Karma: +566/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query finding elements without specific child element
« Reply #3 on: March 17, 2025, 10:32:45 pm »
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.

Code: [Select]
Select * from t_object o where o.Object_Type = 'SpecificType'Should give you a clue to figure out what to look for.

Geert

PS. I remember a weird behavior in one of the version of EA that an empty line was interpreted as the end of the query. The rest was simply not processed. Try removing the empty lines to see if that helps.

M1TO

  • EA User
  • **
  • Posts: 22
  • Karma: +0/-0
    • View Profile
Re: SQL query finding elements without specific child element
« Reply #4 on: March 17, 2025, 11:05:21 pm »
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.
That is not a problem and works just fine.

PS. I remember a weird behavior in one of the version of EA that an empty line was interpreted as the end of the query. The rest was simply not processed. Try removing the empty lines to see if that helps.
That sounds really weird.  :o  Tried it, but doesn't change anything.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13387
  • Karma: +566/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query finding elements without specific child element
« Reply #5 on: March 17, 2025, 11:12:44 pm »
The query seems correct.
Maybe you should first figure out how to find the elements that have such a child element.
That is not a problem and works just fine.
So you mean that your query with "WHERE EXISTS" works perfectly (get you the elements), but "WHERE NOT EXISTS" also includes the same elements?

That seems hard to believe. There must be something else playing. An element can't be on both datasets of they have opposit criteria.

Geert

M1TO

  • EA User
  • **
  • Posts: 22
  • Karma: +0/-0
    • View Profile
Re: SQL query finding elements without specific child element
« Reply #6 on: March 17, 2025, 11:34:48 pm »
I had the query with the existing elements a little bit different and that worked, yes. But I found the reason: The Object_Type of the element is different to the one in the Properties Window. Now I use the Stereotype instead, and it works.  :)

Sometimes "talking" about the problem helps. Thanks Geert.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 13387
  • Karma: +566/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: SQL query finding elements without specific child element
« Reply #7 on: March 17, 2025, 11:45:06 pm »
I had the query with the existing elements a little bit different and that worked, yes. But I found the reason: The Object_Type of the element is different to the one in the Properties Window. Now I use the Stereotype instead, and it works.  :)

Sometimes "talking" about the problem helps. Thanks Geert.
Yes, the properties show the "metatype" as type, not the actual value in Object_Type

Using the stereotype is in most cases the better option.

Geert