Metaverse SQL Queries

/* Find metaverse objects with n connectors*/

SELECT
*FROM dbo.mms_metaverse m
INNER JOIN(
 SELECT mv_object_id
 FROM dbo.mms_csmv_link
 GROUPBY mv_object_id
 HAVING(COUNT(*)= n )) c
ON
m.object_id= c.mv_object_id

 

/* Select all objects in CS */

 select *from dbo.mms_connectorspace cs
join dbo.mms_management_agent ma
 on cs.ma_id = ma.ma_id
 where ma.ma_name =‘Notes’
 

 /* Select joined objects in CS */

 select *from dbo.mms_connectorspace cs
join dbo.mms_management_agent ma
on cs.ma_id = ma.ma_id
join dbo.mms_csmv_link mv
on mv.cs_object_id = cs.object_id
where ma.ma_name =‘Notes’
 

/* Select objects joined from two CS – get the CS ids from table mms_management_agent */

select distinct mc.mv_object_id, mv.firstName, mv.lastName, mv.mail
from dbo.mms_csmv_link mc
join dbo.mms_csmv_link m1 on mc.mv_object_id=m1.mv_object_id
join dbo.mms_connectorspace cs1 on cs1.object_id= m1.cs_object_id
join dbo.mms_csmv_link m2 on mc.mv_object_id=m2.mv_object_id
join dbo.mms_connectorspace cs2 on cs2.object_id= m2.cs_object_id
join dbo.mms_metaverse mv on mc.mv_object_id = mv.object_id
where cs1.ma_id=‘9EF243BA-DB31-411C-BDAF-7DB5925995F9’
and cs2.ma_id=‘6CDE8088-67D4-418C-957D-01351C276D87’