SQL Query to find elements that are missing a connection

[netizensmith]

Hi all,

My SQL is super rusty. I'm trying to find all elements of stereotype "A" that to not have any connections of type "B" (let's say an association for example) with an element of stereotype "C" at the other end. I can find the inverse easy enough (Every A that has a C at the other end) but not sure where to start. Can anyone help?
Here's the opposite of what I want:

Code: [Select]

SELECT  goal_obj.ea_guid AS CLASSGUID, goal_obj.Name AS goalName,
outcome_obj.name AS outcomeName, package_obj.Package_ID AS PackageID
FROM t_object goal_obj

INNER JOIN t_package package_obj ON goal_obj.package_id = package_obj.package_id
INNER JOIN t_connector goal_outcome_connector ON goal_obj.object_id = goal_outcome_connector.end_object_id
INNER JOIN t_object outcome_obj ON goal_outcome_connector.start_object_id = outcome_obj.object_id AND outcome_obj.Stereotype = 'ArchiMate_Outcome'

WHERE goal_obj.Stereotype = 'ArchiMate_Goal'
--AND outcome_obj IS NULL
AND package_obj.Package_ID = 2396;-- 2396 is the Package ID of REDACTED


[Geert Bellekens]

You'll have to use a
where not exists( <selec query>)

Geert

[netizensmith]

I forgot to thank you for this Geert, thanks.