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; } } }

Assistance writing a SQL query to count pages with specific labels

Daniel Knapp

Hello,

I have this query which counts the number of pages with an ancestorID:

SELECT COUNT(*) FROM CONFANCESTORS WHERE ancestorid = '123830295';

And a query which counts all pages with specific labels:

SELECT COUNT(*) FROM CONTENT WHERE CONTENTID IN (SELECT CONTENTID FROM CONTENT_LABEL WHERE LABELID IN (SELECT LABELID FROM LABEL WHERE NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae')));

How do I 'merge' the query so I get the total number of pages with the labels: 'os-ae','os-ae-r','os-dk','os-n-ae' with the parent page: 123830295

Many thanks

Accepted answers

NotTheRealStephenSifersNOPENOPENOPENOPE

Hey Daniel,

Give this a try:

SELECT COUNT(*)
FROM CONTENT C1
JOIN CONTENT_LABEL C2 ON C1.CONTENTID = C2.CONTENTID
JOIN LABEL L1 ON C2.LABELID = L1.LABELID
WHERE L1.NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae')
AND C1.PARENTID = '123830295'

All comments

NotTheRealStephenSifersNOPENOPENOPENOPE

Hey Daniel,

Give this a try:

SELECT COUNT(*)
FROM CONTENT C1
JOIN CONTENT_LABEL C2 ON C1.CONTENTID = C2.CONTENTID
JOIN LABEL L1 ON C2.LABELID = L1.LABELID
WHERE L1.NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae')
AND C1.PARENTID = '123830295'

Daniel Knapp

Hi Stephen,

Thank you for your reply.

Running your query only returns a count of 19. We have around 300 pages that is a child page of parentID 123830295.

I know for a fact that at least 250 of these pages are labelled with one of os-ae, os-ae-r, os-dk or os-n-ae. Any ideas why it's returning such a low number?

NotTheRealStephenSifersNOPENOPENOPENOPE

I understand a little bit more of how you're wanting to find these now. Thank you.

I am digging into this further to get you an answer. For some reason I have not discovered yet, the numbers in the SQL query even on my end are not reconciling with the numbers I get back from Confluence.

Prime example:

I search Confluence KB space for labels with: kb-how-to-article

I see the following: Page 1 of 35. Showing 349 results (0.009 seconds)

I query SQL for label: kb-how-to-article

I see the following: (274 rows affected)

Still looking into this.

NotTheRealStephenSifersNOPENOPENOPENOPE

So instead of parentID I used space ID and was able to return the same count as the Confluence search:

SELECT COUNT(*)
FROM CONTENT C1
JOIN CONTENT_LABEL C2 ON C1.CONTENTID = C2.CONTENTID
JOIN LABEL L1 ON C2.LABELID = L1.LABELID
WHERE (L1.NAME IN ('os-ae','os-ae-r','os-dk','os-n-ae'))
AND (C1.SPACEID = '123830295')
AND C1.CONTENT_STATUS = 'current'

Welcome!

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

Quick Links

Find more posts tagged with

confluence

server

confluence-server