Concatenation of Records via FOR XML PATH - SQL SERVER

Question

Good afternoon dear, I have the following problem:

I am developing a Query where I need to verify which documents were delivered by the student, documents that are in the table pre_matricula_doc and have the following structure:

Then the pre-enrollment student 2 presented documents 1 and 2, and the pre-enrollment student 3 presented document 3.

The documents that were presented by the student "2" o 1 corresponds to CPF, o 2 the RG, student "3" It is the birth certificate.

I am bringing these documents with the following query:

SELECT      '; ' + DA.DESCRICAO
FROM        BD.pre_matricula_doc PDA
LEFT JOIN   BD.DOCUMENTOS DA ON PDA.pre_matricula   IN (1, 2)
                            AND DA.CODIGO           = PDA.documento 
                            AND PDA.apresentado     = '1'       
GROUP BY    PDA.pre_matricula
        ,   DA.descricao

The PDA table is the table where the pre-registration documents are (which I put the structure above) and the DA table is where the description of the documents is (CPF, RG, Birth Certificate).

It happens that when I bring this information to only one student the query works correctly, however, when I mention more than one code in CLAUSE IN the query concatenates all documents and brings back to all students, see:

Aluno     Documentos apresentados

-----------------------------------

2         RG; CPF; Certidão de nascimento
3         RG; CPF; Certidão de nascimento

When the desired result would be:

Aluno     Documentos apresentados

-----------------------------------

2         RG; CPF;
3         Certidão de nascimento;

What can I do to bring you the right information?

Thanks in advance.

Answer 1

In your query you are placing filters directly on LEFT JOIN when they should be in the clause WHERE:

SELECT      '; ' + DA.DESCRICAO
FROM        BD.pre_matricula_doc    PDA
LEFT JOIN   BD.DOCUMENTOS           DA  ON DA.CODIGO = PDA.documento
WHERE       PDA.pre_matricula   IN (2, 3)
        AND PDA.apresentado     = '1'
GROUP BY    PDA.pre_matricula
        ,   DA.descricao

With the filter PDA.pre_matricula IN (1, 2) will only bring those with value 1 or 2 of the column pre_matricula (typo? ), hence changed to IN (2, 3), where it can obtain information for values 2 and 3.