{"id":132,"date":"2008-07-15T09:45:09","date_gmt":"2008-07-15T09:45:09","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?page_id=132"},"modified":"2012-04-22T23:55:15","modified_gmt":"2012-04-22T23:55:15","slug":"sql-query-find-metaverse-objects-with-n-connectors","status":"publish","type":"page","link":"https:\/\/www.wapshere.com\/missmiis\/sql-query-find-metaverse-objects-with-n-connectors","title":{"rendered":"Metaverse SQL Queries"},"content":{"rendered":"

\/* Find metaverse objects with n<\/em> connectors*\/<\/span><\/p>\n

SELECT
\n<\/span><\/span>*<\/span>FROM<\/span> dbo<\/span>.<\/span>mms_metaverse m<\/span>
\nINNER <\/span><\/span>JOIN<\/span>(<\/span>
\n\u00c2\u00a0<\/span><\/span>SELECT<\/span> mv_object_id
\n<\/span><\/span>\u00c2\u00a0FROM<\/span> dbo<\/span>.<\/span>mms_csmv_link
\n<\/span><\/span>\u00c2\u00a0GROUP<\/span>BY<\/span> mv_object_id
\n<\/span><\/span>\u00c2\u00a0HAVING<\/span>(<\/span>COUNT<\/span>(<\/span>*<\/span>)<\/span>=<\/span>\u00c2\u00a0n<\/em> <\/span>)) <\/span><\/span>c<\/span>
\nON
\n<\/span><\/span>m<\/span>.<\/span>object_id<\/span>=<\/span> c<\/span>.<\/span>mv_object_id<\/span><\/p>\n

\u00c2\u00a0<\/span><\/p>\n

\/* Select all objects in CS *\/<\/span><\/span><\/p>\n

\u00c2\u00a0<\/span><\/span>select <\/span><\/span>*<\/span><\/span>from<\/span><\/span> dbo<\/span>.<\/span><\/span>mms_connectorspace cs<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_management_agent ma <\/span>
\n\u00c2\u00a0<\/span>on <\/span><\/span>cs<\/span>.<\/span><\/span>ma_id <\/span>=<\/span><\/span> ma<\/span>.<\/span><\/span>ma_id<\/span>
\n\u00c2\u00a0<\/span>where <\/span><\/span>ma<\/span>.<\/span><\/span>ma_name <\/span>=<\/span><\/span>‘Notes’<\/span><\/span>
\n\u00c2\u00a0<\/span><\/p>\n

\u00c2\u00a0<\/span>\/* Select joined objects in CS *\/<\/span><\/span><\/p>\n

\u00c2\u00a0<\/span>select <\/span><\/span>*<\/span><\/span>from<\/span><\/span> dbo<\/span>.<\/span><\/span>mms_connectorspace cs <\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_management_agent ma <\/span>
\non <\/span><\/span>cs<\/span>.<\/span><\/span>ma_id <\/span>=<\/span><\/span> ma<\/span>.<\/span><\/span>ma_id<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_csmv_link mv<\/span>
\non <\/span><\/span>mv<\/span>.<\/span><\/span>cs_object_id <\/span>=<\/span><\/span> cs<\/span>.<\/span><\/span>object_id<\/span><\/span>
\nwhere <\/span><\/span>ma<\/span>.<\/span><\/span>ma_name <\/span>=<\/span><\/span>‘Notes’<\/span><\/span>
\n\u00c2\u00a0<\/span><\/span><\/p>\n

\/* Select objects joined from two CS – get the CS ids from table mms_management_agent\u00c2\u00a0*\/<\/span><\/span><\/p>\n

select <\/span><\/span>distinct<\/span><\/span> mc<\/span>.<\/span><\/span>mv_object_id<\/span>,<\/span><\/span> mv<\/span>.<\/span><\/span>firstName<\/span>,<\/span><\/span> mv<\/span>.<\/span><\/span>lastName<\/span>,<\/span><\/span> mv<\/span>.<\/span><\/span>mail <\/span>
\nfrom <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_csmv_link mc<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_csmv_link m1 <\/span>on<\/span><\/span> mc<\/span>.<\/span><\/span>mv_object_id<\/span>=<\/span><\/span>m1<\/span>.<\/span><\/span>mv_object_id<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_connectorspace cs1 <\/span>on<\/span><\/span> cs1<\/span>.<\/span><\/span>object_id<\/span><\/span>=<\/span><\/span> m1<\/span>.<\/span><\/span>cs_object_id<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_csmv_link m2 <\/span>on<\/span><\/span> mc<\/span>.<\/span><\/span>mv_object_id<\/span>=<\/span><\/span>m2<\/span>.<\/span><\/span>mv_object_id<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_connectorspace cs2 <\/span>on<\/span><\/span> cs2<\/span>.<\/span><\/span>object_id<\/span><\/span>=<\/span><\/span> m2<\/span>.<\/span><\/span>cs_object_id<\/span>
\njoin <\/span><\/span>dbo<\/span>.<\/span><\/span>mms_metaverse mv <\/span>on<\/span><\/span> mc<\/span>.<\/span><\/span>mv_object_id <\/span>=<\/span><\/span> mv<\/span>.<\/span><\/span>object_id<\/span><\/span>
\nwhere <\/span><\/span>cs1<\/span>.<\/span><\/span>ma_id<\/span>=<\/span><\/span>‘9EF243BA-DB31-411C-BDAF-7DB5925995F9’<\/span><\/span>
\nand <\/span><\/span>cs2<\/span>.<\/span><\/span>ma_id<\/span>=<\/span><\/span>‘6CDE8088-67D4-418C-957D-01351C276D87’<\/span><\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

\/* Find metaverse objects with n connectors*\/ SELECT *FROM dbo.mms_metaverse m INNER JOIN( \u00c2\u00a0SELECT mv_object_id \u00c2\u00a0FROM dbo.mms_csmv_link \u00c2\u00a0GROUPBY mv_object_id \u00c2\u00a0HAVING(COUNT(*)=\u00c2\u00a0n )) c ON m.object_id= c.mv_object_id \u00c2\u00a0 \/* Select all objects in CS *\/ \u00c2\u00a0select *from dbo.mms_connectorspace cs join dbo.mms_management_agent ma \u00c2\u00a0on cs.ma_id = ma.ma_id \u00c2\u00a0where ma.ma_name =‘Notes’ \u00c2\u00a0 \u00c2\u00a0\/* Select joined objects in CS *\/…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-132","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/Pkp1o-28","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=132"}],"version-history":[{"count":9,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/132\/revisions"}],"predecessor-version":[{"id":136,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/pages\/132\/revisions\/136"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}