Nrthugu

I have a table which has tags which reference another table in multiple languages. I wrote this query which does the job but isn't elegant and its very long winded:

SELECT DISTINCT

    T1.ID as CountryID,

    T1.Type AS CountryType,

    T2.Text AS CountryText,

    T3.Text AS CountryTitle,

    T4.Text AS Heading1,

    T5.Text AS Heading2,

    T6.Text AS Heading3,

    T7.Text AS Heading4,

    T8.Text AS Heading5,

    T9.Text AS Heading6,

    T10.Text AS Heading7,

    T11.Text AS Heading8,

    T12.Text AS Heading9,

    T13.Text AS Heading10,

    T14.Text AS Heading11,

    T15.Text AS Heading12,

    T16.Text AS Heading13,

    T17.Text AS Heading14,

    T18.Text AS Heading15,

    T19.Text AS Heading16,

    T20.Text AS Heading17,

    T21.Text AS Heading18,

    T22.Text AS Heading19,

    T23.Text AS Heading20

FROM 

    Countrys AS T1

    LEFT JOIN EN_TEXT AS T2 ON T1.DESCRIPTION=T2.TAG

    LEFT JOIN EN_TEXT AS T3 ON CONCAT(T1.COUNTRYTAG,'_TITLE')=T3.TAG

    LEFT JOIN EN_TEXT AS T4 ON CONCAT(T1.COUNTRYTAG,'_HEADING_1')=T4.TAG

    LEFT JOIN EN_TEXT AS T5 ON CONCAT(T1.COUNTRYTAG,'_HEADING_2')=T5.TAG

    LEFT JOIN EN_TEXT AS T6 ON CONCAT(T1.COUNTRYTAG,'_HEADING_3')=T6.TAG

    LEFT JOIN EN_TEXT AS T7 ON CONCAT(T1.COUNTRYTAG,'_HEADING_4')=T7.TAG

    LEFT JOIN EN_TEXT AS T8 ON CONCAT(T1.COUNTRYTAG,'_HEADING_5')=T8.TAG

    LEFT JOIN EN_TEXT AS T9 ON CONCAT(T1.COUNTRYTAG,'_HEADING_6')=T9.TAG

    LEFT JOIN EN_TEXT AS T10 ON CONCAT(T1.COUNTRYTAG,'_HEADING_7')=T10.TAG

    LEFT JOIN EN_TEXT AS T11 ON CONCAT(T1.COUNTRYTAG,'_HEADING_8')=T11.TAG

    LEFT JOIN EN_TEXT AS T12 ON CONCAT(T1.COUNTRYTAG,'_HEADING_9')=T12.TAG

    LEFT JOIN EN_TEXT AS T13 ON CONCAT(T1.COUNTRYTAG,'_HEADING_10')=T13.TAG

    LEFT JOIN EN_TEXT AS T14 ON CONCAT(T1.COUNTRYTAG,'_HEADING_11')=T14.TAG

    LEFT JOIN EN_TEXT AS T15 ON CONCAT(T1.COUNTRYTAG,'_HEADING_12')=T15.TAG

    LEFT JOIN EN_TEXT AS T16 ON CONCAT(T1.COUNTRYTAG,'_HEADING_13')=T16.TAG

    LEFT JOIN EN_TEXT AS T17 ON CONCAT(T1.COUNTRYTAG,'_HEADING_14')=T17.TAG

    LEFT JOIN EN_TEXT AS T18 ON CONCAT(T1.COUNTRYTAG,'_HEADING_15')=T18.TAG

    LEFT JOIN EN_TEXT AS T19 ON CONCAT(T1.COUNTRYTAG,'_HEADING_16')=T19.TAG

    LEFT JOIN EN_TEXT AS T20 ON CONCAT(T1.COUNTRYTAG,'_HEADING_17')=T20.TAG

    LEFT JOIN EN_TEXT AS T21 ON CONCAT(T1.COUNTRYTAG,'_HEADING_18')=T21.TAG

    LEFT JOIN EN_TEXT AS T22 ON CONCAT(T1.COUNTRYTAG,'_HEADING_19')=T22.TAG

    LEFT JOIN EN_TEXT AS T23 ON CONCAT(T1.COUNTRYTAG,'_HEADING_20')=T23.TAG

WHERE

    T1.Type='Country_ENGLAND';

Is there a way I can simplify this query so that I don't have to have all those LEFT JOINS? Different countries will have different number of headings so I have to get all possible headings to ensure I get them all.

Something like this - I know this wont work, I'm just giving an example of what I am trying to do.

SELECT DISTINCT

T1.ID as CountryID,

T1.Type AS CountryType,

T2.Text AS CountryText,

T3.Text AS CountryTitle,

T4.Text AS Headings?,



FROM 

Countrys AS T1

LEFT JOIN EN_TEXT AS T2 ON T1.DESCRIPTION=T2.TAG

LEFT JOIN EN_TEXT AS T3 ON CONCAT(T1.COUNTRYTAG,'_TITLE')=T3.TAG

LEFT JOIN EN_TEXT AS T4 ON CONCAT(T1.COUNTRYTAG,'_HEADING_%')=T4.TAG AND T4.TAG IS NOT NULL,

WHERE

T1.Type='Country_ENGLAND';

You could use conditional aggregation to pivot the tags

SELECT DISTINCT

    C.ID as CountryID,

    C.Type AS CountryType,

    MAX(CASE WHEN C.DESCRIPTION = T.TAG THEN T.Text END) AS CountryText,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_TITLE') THEN T.Text END) AS CountryTitle,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_1') THEN T.Text END) AS Heading1,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_2') THEN T.Text END) AS Heading2,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_3') THEN T.Text END) AS Heading3,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_4') THEN T.Text END) AS Heading4,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_5') THEN T.Text END) AS Heading5,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_6') THEN T.Text END) AS Heading6,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_7') THEN T.Text END) AS Heading7,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_8') THEN T.Text END) AS Heading8,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_9') THEN T.Text END) AS Heading9,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_10') THEN T.Text END) AS Heading10,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_11') THEN T.Text END) AS Heading11,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_12') THEN T.Text END) AS Heading12,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_13') THEN T.Text END) AS Heading13,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_14') THEN T.Text END) AS Heading14,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_15') THEN T.Text END) AS Heading15,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_16') THEN T.Text END) AS Heading16,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_17') THEN T.Text END) AS Heading17,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_18') THEN T.Text END) AS Heading18,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_19') THEN T.Text END) AS Heading19,

    MAX(CASE WHEN T.TAG = CONCAT(C.COUNTRYTAG,'_HEADING_20') THEN T.Text END) AS Heading20

FROM Countrys AS C

LEFT JOIN EN_TEXT AS T 

  ON T.TAG LIKE CONCAT(C.COUNTRYTAG,'%')

  -- AND (T.TAG LIKE '%TITLE' OR T.TAG LIKE '%HEADING%')

WHERE C.Type = 'Country_ENGLAND'

GROUP BY C.ID, C.Type;

Well, it's still a bit long winded.

But it only needs 1 join, so it should be faster.

And in the criteria used in the CASE's you could instead use a LIKE.

Because the ON clause already assures the COUNTRYTAG.

F.e.

... MAX(CASE WHEN T.TAG LIKE '%HEADING_1' THEN T.Text END) AS Heading1, ...

But then you'll have to take the calculated risk that in a LIKE an underscore _ is a placeholder for any 1 character.

Your design looks OK-ish but your query does not. Having one rows per translation is perhaps better than having one row with a dozen columns. Just build a list, or perhaps a table, of all tags you're interested in and left join with it:

SELECT COUNTRYS.ID, COUNTRYS.TYPE, TAGLIST.TAG, EN_TEXT.Text

FROM COUNTRYS 

CROSS JOIN (

    SELECT 'TITLE' AS TAG

    SELECT 'HEADING_1'

    SELECT 'HEADING_2'

    SELECT 'HEADING_3'

    SELECT 'HEADING_4'

    SELECT 'HEADING_5'

    SELECT 'HEADING_6'

    SELECT 'HEADING_7'

    SELECT 'HEADING_8'

    SELECT 'HEADING_9'

    SELECT 'HEADING_10'

    SELECT 'HEADING_11'

    SELECT 'HEADING_12'

    SELECT 'HEADING_13'

    SELECT 'HEADING_14'

    SELECT 'HEADING_15'

    SELECT 'HEADING_16'

    SELECT 'HEADING_17'

    SELECT 'HEADING_18'

    SELECT 'HEADING_19'

    SELECT 'HEADING_20'

) AS TAGLIST

LEFT JOIN EN_TEXT ON 

    EN_TEXT.TAG = COUNTRYS.DESCRIPTION OR

    EN_TEXT.TAG = CONCAT(COUNTRYS.COUNTRYTAG, '_', TAGLIST.TAG)

WHERE COUNTRYS.TYPE = 'Country_ENGLAND'

This will give you one row (or may be more if you have duplicates) per tag, or NULL in the text column if matching translation was not found.