Database SELECT to get all label from a specific space

Question

Hello together,

I search a SELECT to get a list of all labels in a specific space (by SPACE-ID) on the database.

I have already tried a few things here, but nothing gave the right result.

Yes I know, it gives a macro with options, that I got all labels from a specific space. But I think the macro gives to much results.

Can anyone help me?

Dominic Lagger · Accepted Answer

Hi @Henning Karl

I tried a bit and think got a good statement:

Select cl.labelid, l.name, s.spacekey, count(l.name) as count from content_label cl
join label l on l.labelid = cl.labelid
join content c on c.contentid = cl.contentid
join spaces s on s.spaceid = c.spaceid
where c.CONTENT_STATUS != 'deleted' and c.contenttype != 'SPACEDESCRIPTION' and s.spacekey = 'SPACEKEY' group by cl.labelid, s.spacekey, l.name
order by count desc;

This makes a list of all labels of a space. But not "Spacedescriptions" and not deleted pages.

Hope this is a good hint for you.

Regards, Dominic