SQL Search on unassigned elements?

[Guillaume]

Hi

I'm working on an Archimate model with Application Components linked to Business Services via a Serving association.

A simple SQL Search lists all application/service links:

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name AS 'Application'
, bizservice.Name AS 'Service'
FROM ((t_object inner join t_connector on t_object.Object_ID = t_connector.Start_Object_ID) 
inner join t_object bizservice on bizservice.Object_ID = t_connector.End_Object_ID)   
WHERE t_object.Stereotype = 'ArchiMate_ApplicationComponent'
and bizservice.Stereotype = 'ArchiMate_BusinessService'
and t_connector.Stereotype = 'ArchiMate_Serving'
I'm having issues in finding a proper to list all the applications that do not have any serving stereotyped connector to a stereotyped business service.

The following is incorrect as it only find applications that are not linked with anything

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name AS 'Application'
FROM (t_object left join t_connector on t_object.Object_ID = t_connector.Start_Object_ID) 
WHERE t_object.Stereotype = 'ArchiMate_ApplicationComponent'
and t_connector.Start_Object_ID is null
I'm not sure there's a suitable query. Any hint?

[Geert Bellekens]

I would use a
where o.ObjectID not in (select ... [put here the query that returns the objects that are linked])

Another solution would be to do a left join with a subquery that joins t_connector and t_object as target

Geert

[Guillaume]

Hi Geert,

Your first suggestion worked perfectly.

Thanks !