SQL returns two records for a field, how do you join them?

Question

SQL returns two records for a field, how do you join them?

Navigation

#1 by (4 votes)

1

I have the following SQL code in DB2:

SELECT p.nome_pessoa, a.dt_nascimento, alc.matricula, d.numero_documento, d.id_tdoc_pessoa FROM pessoas as p
INNER JOIN alunos as a ON p.id_pessoa = a.id_pessoa
INNER JOIN acad_alunos_cursos as alc ON alc.id_aluno = a.id_aluno
INNER JOIN doc_pessoas as d ON p.id_pessoa = d.id_pessoa WHERE p.nome_pessoa = 'Vitor Lucas Pires Cordovil'

After executing the query I have the following return:

NAME - DATE OF BIRTH - REGISTRATION - NUMBER DOCUMENT - DOCUMENT TYPE

Vitor Lucas Pires Cordovil - 1994-02-27 - 2014300028 - 951.249.865-X3 - 1

Vitor Lucas Pires Cordovil - 1994-02-27 - 2012430028 - 10491872 - 3

It returns me the two records, because there are two documents registered to the person, when the document type is 1 then it is an RG, when it is 3 is a CPF. But I want the query to return only one record with two columns: RG and CPF, all on the same line. How to make?

sql db2

asked by anonymous 22.12.2014 / 17:39

1 answer

Windows form c # identify which button of the BindingNavigator was clicked Opening dialog () not working

score 4 · Accepted Answer

I would do something like this:

SELECT p.nome_pessoa, a.dt_nascimento, alc.matricula, df.numero_documento as RG, dj.numero_documento as CPF, df.id_tdoc_pessoa as df, dj.id_tdoc_pessoa as dj FROM pessoas as p
INNER JOIN alunos as a ON p.id_pessoa = a.id_pessoa
INNER JOIN acad_alunos_cursos as alc ON alc.id_aluno = a.id_aluno
LEFT JOIN doc_pessoas as df ON p.id_pessoa = df.id_pessoa and df.id_tdoc_pessoa = 1
LEFT JOIN doc_pessoas as dj ON p.id_pessoa = dj.id_pessoa and dj.id_tdoc_pessoa = 3
WHERE p.nome_pessoa = 'Vitor Lucas Pires Cordovil'

What would be to give a new JOIN in the table doc_people, restricting for each specific type, in its case 1 and 3. I used LEFT instead of INNER so that the line is displayed even if there is no value for type = 1 or 3.