CODEWITHSUNDEEP
phpmysql
Flex60460
Flex60460

Reputation: 993

GROUP_CONCAT strange result

I try to use group_concat to create more quickly xml outpout. The record number is different between traditional query. Indeed, when my query use group concat, I have less record.

    SELECT GROUP_CONCAT(
    CONCAT('\n<p>\n',  
        CONCAT('\n<id>',paIndex,'</id>\n'),
        CONCAT('<prInitiales>',prInitiales,'</prInitiales>\n'),
        CONCAT('<paNomPren>',paNomPrenom,'\n',ttT_Traitement_P,'</paNomPren>\n'),                                   
        CONCAT('<ttTStatutP>',ttTStatutP,' - ',DATE_FORMAT(ttDateStatut,'%d/%m/%Y'),'\n',ttUserImportant,'</ttTStatutP>\n'),
        CONCAT('<paDossier1>',paDossier1,'\n',paDossier2,'</paDossier1>\n'),
        CONCAT('<paNumTel1>',paNumTel1,'\n',paNumTel2,'</paNumTel1>\n'),
        CONCAT('<paNaissanceS>',DATE_FORMAT(paNaissance,'%d/%m/%Y'),'</paNaissanceS>\n'),
    '</p>') ORDER BY paNomPrenom DESC) AS xml
    FROM 20Patients_1012
    JOIN 30Traitemnt_201223 ON 20Patients_1012.paIndex = 30Traitemnt_201223.ttIndex
    JOIN 12Praticien_02 ON 30Traitemnt_201223.ttPraticien = 12Praticien_02.prIndex

The traditional query:

    SELECT 20Patients_1012.paIndex, 20Patients_1012.paNomPrenom, 20Patients_1012.paDossier1, 20Patients_1012.paDossier2, 20Patients_1012.paNaissance, 20Patients_1012.paNumTel1, 30Traitemnt_201223.ttTStatutP, 30Traitemnt_201223.ttDateStatut, 12Praticien_02.prInitiales
FROM 20Patients_1012
JOIN 30Traitemnt_201223 ON 20Patients_1012.paIndex = 30Traitemnt_201223.ttIndex
JOIN 12Praticien_02 ON 30Traitemnt_201223.ttPraticien = 12Praticien_02.prIndex ORDER BY 20Patients_1012.paNomPrenom ASC

Thanks for helping

Upvotes: 0

Views: 111

Answers (3)

Flex60460
Flex60460

Reputation: 993

I found the solution. Indeed, if null value, record is ignored, so I use, COALESCE function to solve that!

Upvotes: 0

marcochiesi
marcochiesi

Reputation: 291

You used LEFT JOIN in a query and simple JOIN in the other. This may lead to different results because LEFT JOIN also consider records that do not have a match in the other table.

Upvotes: 0

eggyal
eggyal

Reputation: 126025

As stated in the manual:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Upvotes: 2

Related Questions