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

Not working - Trying to display DATA from different Tables using Multiple JOINs

Ramu_kd54ib

Hey .. i'm trying to display data from different Tables with the following SQL code:

SELECT
T1.'Key',
T1.'Status',
T2.'Key' AS 'NBS Key',
T2.'Status' AS 'NBS Status',
T3.'Key' AS 'EHOT Key',
T3.'Status' AS 'EHOT Status',
T4.'Key' AS 'EPT Key',
T4.'Status' AS 'EPT Status',
T5.'Key' AS 'OCT Key',
T5.'Status' AS 'OCT Status'
FROM T1 JOIN T2 ON (T2.'Linked Issues' = T1.'Key')
AND T1 JOIN T3 ON ( T3.'Linked Issues' = T1.'Key' )
AND T1 JOIN T4 ON ( T4.'Linked Issues' = T1.'Key' )
AND T1 JOIN T5 ON ( T5.'Linked Issues' = T1.'Key' )

Im using a 'Table Transformation' in Confluence and trying the aforementioned code to display 'Key' & 'Status' from 4 different Tables (all based on the comparison with Table 1 'T1.Key' ) ..

now, this syntax is not returning any error .. it just returns 'The Query returned no rows' message as follows:

From the above screenshot, please ignore the numbers & issues .. these are nothing but the count of issues(Tickets) i have in all those 5 Tables ...

The same code works well and return the data when i have ONLY 2 Tables with one JOIN condition instead of 5 ..

SO, when i try to add more tables and add multiple JOIN conditions, this is not working ..

stiltsoft OR anyone of you ... Please help me on this ...

Accepted answers

All comments

Stiltsoft support

Hi @Ramu ,

You may check this Community question for reference and adjust the syntax of your JOIN conditions.

The "AND T1" part is not required for the JOIN conditions.

Ramu_kd54ib

Hi ..

Thank you ..

I have tried referring the aforementioned link(another page) and did my tryouts with different combn of JOINS .. finally i was able to bring the Table as follows:

JOINS latest.jpg

The 'Key' in Table1 will be across in Table 2 & 3 as 'Parent Link' ..

(T1.key tickets .. will be the Parent Link across all T2 & T3 tickets) ..

So .. im trying to get all the T2 & T3 records based on JOIN cond: of matching with T1 'Key' and my code as follows:

This code has returned the Table pic i pasted above .. but NOT the exact result .. means this displayed/returned bunch of TECH tickets(T1) and some corresponding and some different(not corresponding) T2 & T3 records ... also, the display was looking ODD as in first TECH tickets displayed and when i scroll down further after the completion of display of Tech tickets, it displayed NBS(T2) and then later T3(EHOT) in the same fashion as following sample:

Have tried few tryouts of diff JOIN combn:(INNER, LEFT JOINS as well) .. but kinda NOT getting the expected result ...

Please see if you can help further on this ..

Stiltsoft support

And if you use just simple JOIN without any prefixes? How it works then?

The prefix LEFT from the given example was used because the main table was T3 for that case. And your initial query seemed to look correct except the "AND T1" parts.

If the issue persists, you may refer to our support. Attach the screenshots of your T1 (main table), T2 and T3 tables (the Jira Issue macros with visible headers and several rows of data in a published state without any macros) and the page storage of your page (upper right corner -> menu ... -> View storage format).

With the help of the storage we'll recreate exactly your macro with the current SQL query. The Jira Issues macros won't be recreated, so we'll need their screenshots.

Ramu_kd54ib

1. Exactly .. that initial code MUST work very fine once i removed 'AND T1' part right but it was NOT ....

when i tried simple JOIN without any prefixes .. i was getting the same 'Query returned no rows' response (the same screen pasted int he initial chat) ..

2. Unfortunately i dont have /cant see any 'Storage limit' details as it is restricted .. however, i have detailed everything STEP by STEP in the attached WORD doc .. It has all the details with appropriate screens in depth ..

<given no option to attach, i have pasted the screens of the Word doc as follows>:

On Other thought, this could be any 3 Tables with the details i have mentioned in the screens above and easy to replicate for you i believe ..

Keeping my hopes high on this one ..

Please see what you can do here ... really helps a lot ...

Welcome!

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

Quick Links

Find more posts tagged with

confluence

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets

unknown-hosting-type