nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

Pete Singleton

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'

All comments

Pete Singleton

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

sql

space