SELECT TAll.* , dbo.getSEOUrlByContentId(TAll.Id, 'IT', TAll.Titolo, '/it/', NULL, TAll.idCompany) AS SEOURL, (SELECT TAss.idContentAssociated AS Id, TC.IdPadre, TP.IdPadre AS IdNonno, TAss.std, ISNULL(CAST(TAss.qta AS VARCHAR(10)),'') AS qta FROM TBLAssociaContentContent TAss INNER JOIN TBLContent TC ON TC.Id = TAss.idContentAssociated LEFT JOIN TBLContent TP ON TP.Id = TC.IdPadre WHERE TAss.idContent = TAll.Id for xml path('content'), root ('root') ) AS AssociatedContents FROM ( SELECT DISTINCT TOP 999999 T0.data,T0.mainHomeImage,T0.youTubeLink,T0.ordina, T0.Colore, T0.linkedContent,T0.id,T0.showData,T0.idCompany,T0.idPadre,T0.pageName,TTLC.testo AS linkedContentTitolo,T1.testo As titolo,TTA.testo AS abstract,TTS.testo AS slogan,TTD.testo AS descrizione,TTO1.pathOggetto AS foto1,TTO2.pathOggetto AS foto2,TTO3.pathOggetto AS foto3,TTO4.pathOggetto AS foto4,TT4.testo AS foto4txt,TTO5.pathOggetto AS foto5,TT5.testo AS foto5txt,TTO6.pathOggetto AS foto6,TT6.testo AS foto6txt,TTO8.pathOggetto AS foto8,TT8.testo AS foto8txt FROM TBLContent T0 INNER JOIN TBLTesti T1 ON T1.idArea = 66 AND T1.idTipoTesto = 6 AND T1.idCampo = T0.id AND T1.idLingua = 01 AND T1.attiva = 1 LEFT JOIN TBLTesti TTA ON TTA.idArea = 66 AND TTA.idTipoTesto = 8 AND TTA.idCampo = T0.id AND TTA.idLingua = 01 LEFT JOIN TBLTesti TTS ON TTS.idArea = 66 AND TTS.idTipoTesto = 60 AND TTS.idCampo = T0.id AND TTS.idLingua = 01 LEFT JOIN TBLTesti TTD ON TTD.idArea = 66 AND TTD.idTipoTesto = 7 AND TTD.idCampo = T0.id AND TTD.idLingua = 01 AND (TTD.ordina = 0 OR TTD.ordina = 1) LEFT JOIN TBLTesti TTLC ON TTLC.idArea = 66 AND TTLC.idTipoTesto = 6 AND TTLC.idCampo = T0.linkedContent AND TTLC.idLingua = 01 LEFT JOIN TBLAssociaOggetti TTO1 ON TTO1.idArea = 66 AND TTO1.tipoOggetto IN (1,2) AND TTO1.idCampo = T0.id AND TTO1.numFoto = 1 AND TTO1.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO2 ON TTO2.idArea = 66 AND TTO2.tipoOggetto IN (1,2) AND TTO2.idCampo = T0.id AND TTO2.numFoto = 2 AND TTO2.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO3 ON TTO3.idArea = 66 AND TTO3.tipoOggetto IN (1,2) AND TTO3.idCampo = T0.id AND TTO3.numFoto = 3 AND TTO3.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO4 ON TTO4.idArea = 66 AND TTO4.tipoOggetto IN (1,2) AND TTO4.idCampo = T0.id AND TTO4.numFoto = 4 AND TTO4.Ordina < 2 LEFT JOIN TBLTestiOggetti TT4 ON TT4.idArea = TTO4.idArea AND TTO4.idCampo = T0.id AND TT4.progressivo = TTO4.progressivo AND TT4.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO5 ON TTO5.idArea = 66 AND TTO5.tipoOggetto IN (1,2) AND TTO5.idCampo = T0.id AND TTO5.numFoto = 5 AND TTO5.Ordina < 2 LEFT JOIN TBLTestiOggetti TT5 ON TT5.idArea = TTO5.idArea AND TTO5.idCampo = T0.id AND TT5.progressivo = TTO5.progressivo AND TT5.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO6 ON TTO6.idArea = 66 AND TTO6.tipoOggetto IN (1,2) AND TTO6.idCampo = T0.id AND TTO6.numFoto = 6 AND TTO6.Ordina < 2 LEFT JOIN TBLTestiOggetti TT6 ON TT6.idArea = TTO6.idArea AND TTO6.idCampo = T0.id AND TT6.progressivo = TTO6.progressivo AND TT6.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO8 ON TTO8.idArea = 66 AND TTO8.tipoOggetto IN (1,2) AND TTO8.idCampo = T0.id AND TTO8.numFoto = 8 AND TTO8.Ordina < 2 LEFT JOIN TBLTestiOggetti TT8 ON TT6.idArea = TTO8.idArea AND TTO8.idCampo = T0.id AND TT8.progressivo = TTO8.progressivo AND TT8.Lingua = 01 INNER JOIN TBLContent TP ON TP.Id = T0.IdPadre WHERE T0.sospendi<>1 AND T0.idPadre = 0211 ) TAll ORDER BY TAll.ordina,TAll.data desc

SELECT TAll.* , dbo.getSEOUrlByContentId(TAll.Id, 'IT', TAll.Titolo, '/it/', NULL, TAll.idCompany) AS SEOURL, (SELECT TAss.idContentAssociated AS Id, TC.IdPadre, TP.IdPadre AS IdNonno, TAss.std, ISNULL(CAST(TAss.qta AS VARCHAR(10)),'') AS qta FROM TBLAssociaContentContent TAss INNER JOIN TBLContent TC ON TC.Id = TAss.idContentAssociated LEFT JOIN TBLContent TP ON TP.Id = TC.IdPadre WHERE TAss.idContent = TAll.Id for xml path('content'), root ('root') ) AS AssociatedContents FROM ( SELECT DISTINCT TOP 999999 T0.data,T0.mainHomeImage,T0.youTubeLink,T0.ordina, T0.Colore, T0.linkedContent,T0.id,T0.showData,T0.idCompany,T0.idPadre,T0.pageName,TTLC.testo AS linkedContentTitolo,T1.testo As titolo,TTA.testo AS abstract,TTS.testo AS slogan,TTD.testo AS descrizione,TTO1.pathOggetto AS foto1,TTO2.pathOggetto AS foto2,TTO3.pathOggetto AS foto3,TTO4.pathOggetto AS foto4,TT4.testo AS foto4txt,TTO5.pathOggetto AS foto5,TT5.testo AS foto5txt,TTO6.pathOggetto AS foto6,TT6.testo AS foto6txt,TTO8.pathOggetto AS foto8,TT8.testo AS foto8txt FROM TBLContent T0 INNER JOIN TBLTesti T1 ON T1.idArea = 66 AND T1.idTipoTesto = 6 AND T1.idCampo = T0.id AND T1.idLingua = 01 AND T1.attiva = 1 LEFT JOIN TBLTesti TTA ON TTA.idArea = 66 AND TTA.idTipoTesto = 8 AND TTA.idCampo = T0.id AND TTA.idLingua = 01 LEFT JOIN TBLTesti TTS ON TTS.idArea = 66 AND TTS.idTipoTesto = 60 AND TTS.idCampo = T0.id AND TTS.idLingua = 01 LEFT JOIN TBLTesti TTD ON TTD.idArea = 66 AND TTD.idTipoTesto = 7 AND TTD.idCampo = T0.id AND TTD.idLingua = 01 AND (TTD.ordina = 0 OR TTD.ordina = 1) LEFT JOIN TBLTesti TTLC ON TTLC.idArea = 66 AND TTLC.idTipoTesto = 6 AND TTLC.idCampo = T0.linkedContent AND TTLC.idLingua = 01 LEFT JOIN TBLAssociaOggetti TTO1 ON TTO1.idArea = 66 AND TTO1.tipoOggetto IN (1,2) AND TTO1.idCampo = T0.id AND TTO1.numFoto = 1 AND TTO1.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO2 ON TTO2.idArea = 66 AND TTO2.tipoOggetto IN (1,2) AND TTO2.idCampo = T0.id AND TTO2.numFoto = 2 AND TTO2.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO3 ON TTO3.idArea = 66 AND TTO3.tipoOggetto IN (1,2) AND TTO3.idCampo = T0.id AND TTO3.numFoto = 3 AND TTO3.Ordina < 2 LEFT JOIN TBLAssociaOggetti TTO4 ON TTO4.idArea = 66 AND TTO4.tipoOggetto IN (1,2) AND TTO4.idCampo = T0.id AND TTO4.numFoto = 4 AND TTO4.Ordina < 2 LEFT JOIN TBLTestiOggetti TT4 ON TT4.idArea = TTO4.idArea AND TTO4.idCampo = T0.id AND TT4.progressivo = TTO4.progressivo AND TT4.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO5 ON TTO5.idArea = 66 AND TTO5.tipoOggetto IN (1,2) AND TTO5.idCampo = T0.id AND TTO5.numFoto = 5 AND TTO5.Ordina < 2 LEFT JOIN TBLTestiOggetti TT5 ON TT5.idArea = TTO5.idArea AND TTO5.idCampo = T0.id AND TT5.progressivo = TTO5.progressivo AND TT5.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO6 ON TTO6.idArea = 66 AND TTO6.tipoOggetto IN (1,2) AND TTO6.idCampo = T0.id AND TTO6.numFoto = 6 AND TTO6.Ordina < 2 LEFT JOIN TBLTestiOggetti TT6 ON TT6.idArea = TTO6.idArea AND TTO6.idCampo = T0.id AND TT6.progressivo = TTO6.progressivo AND TT6.Lingua = 01 LEFT JOIN TBLAssociaOggetti TTO8 ON TTO8.idArea = 66 AND TTO8.tipoOggetto IN (1,2) AND TTO8.idCampo = T0.id AND TTO8.numFoto = 8 AND TTO8.Ordina < 2 LEFT JOIN TBLTestiOggetti TT8 ON TT6.idArea = TTO8.idArea AND TTO8.idCampo = T0.id AND TT8.progressivo = TTO8.progressivo AND TT8.Lingua = 01 INNER JOIN TBLContent TP ON TP.Id = T0.IdPadre WHERE T0.sospendi<>1 AND T0.idPadre = 0362 ) TAll ORDER BY TAll.ordina,TAll.data desc