SQL help needed: List all spaces with description and categories(labels?).

[Andrey Rezvushkin]

Hi!

I want to get all spaces with description and categories. I see some categories in AO_54C900_SPACE_BLUEPRINT_AO, but they are standart. How can i see custom categories? And how i can see description about spaces? 

I look they on Space Directory.

[Pete Singleton · Accepted solution]

Try this:

SELECT S.SpaceKey, L.Name "Type", BC.Body "Description"
FROM SPACES S, CONTENT C, CONTENT_LABEL CL, LABEL L, BODYCONTENT BC
WHERE S.SpaceID=C.SpaceID 
AND C.ContentID=CL.ContentID
AND BC.ContentID=C.ContentID
AND CL.LabelID=L.LabelID
AND L.Namespace ='team'

[Andrey Rezvushkin]

Amaaaaazing! thx you sooo much!

[ITS Support]

This query is almost what I'm after.  It only lists sites that have a category and description.  How can the query be modified to list all sites and include the category and description if available?

[Babs Adewole]

@ITS Support . This should work

select s.spaceid, s.spacekey, s.spacename ,bc.bodydescription,l.name category
from spaces s
left join content c on c.spaceid = s.spaceid and c.contenttype = 'SPACEDESCRIPTION'
left join bodycontent bc on bc.contentid = c.contentid
left join content_label cl on cl.contentid = c.contentid
left join label l on l.labelid = cl.labelid