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

[Ramu]

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 ...

[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]

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:

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]

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 ...

[Stiltsoft support]

The initial code without the "AND T1" parts works just fine:

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'
FROM T1 JOIN T2 ON (T2.'Parent Link' = T1.'Key')
JOIN T3 ON (T3.'Parent Link' = T1.'Key')

You may replicate such manually created tables and check if it works for you.

If everything is ok, you may look into your Jira Issues macros.

On the screenshot I see that you Jira Issues macros are placed inside the Expand macros, as I guess - are they placed directly without any additional macros inside the Table Transformer macro?

If you open your Table Transformer macro with the default SQL query "SELECT * FROM T*", do you see your three Jira tables correctly in the Table Transformer macro preview?

The Storage Format can be downloaded for you by your Confluence administrator and you may attach it through the support portal.

[Ramu]

Hi .. Sorry for the delay in responding ..

Yes .. Manually having those simple tables working for me as well ... but i have JIRA filters as my Tables in the original requirement ...

However, im able to proceed on this by splitting the requirement into silos ... 

THanks again for all of your different ideas/suggestions .. those were really thought provoking ... and helped me to try different .. :) :)

[Stiltsoft support]

The Jira Issues tables should be working as these simple manually created tables unless they are wrapped into some other macros or smth (there should be no any difference for the Table Transformer macro if the source table is a manually created table or a macro generated).

So, I'm glad that it works for you now but in case of other questions you may freely refer to our support portal that is confidential - we'll be able to discuss your cases in details.