Select Distinct

Question

tbl_protocolos

id_protocolo | protocolo | assunto

1;ra001;vazamentos

2;ra002;incendios

3;ra003;inundacoes


tbl_prot_tramitacoes

id_tram | id_protocolo | pdf | id_setor_origem

1;1;001.pdf;70

2;1;001.pdf;100 - **rejeitar por causa do id_setor_origem**

3;2;002.pdf;70

4;3;003.pdf;70

5;1;001.pdf;70 - rejeitar pois já peguei **id_protocolo = 1** na linha 1

I need the tbl_prot_tramitacoes pdf field ( where there is repetition of id_protocolo ) and id_setor_origem = 70 e preciso tb do campo protocolo e assunto da tbl_protocolos ( onde NÃO há repetiçao do id_protocolo )

Now, how do I carry these records ?

id_tram | protocolo | assunto | pdf

1          ra001    vazamentos  001.pdf

3          ra002    incendios   002.pdf

4          ra003    inundacoes  003.pdf

Answer 1

You can use a subquery to select the lines that meet the conditions, as shown below.

SELECT a.id_tram,protocolo, assunto, pdf 
FROM tbl_prot_tramitacoes AS a
INNER JOIN tbl_protocolos AS b
ON a.id_protocolo = b.id_protocolo
WHERE a.id_tram IN 
(SELECT MIN(id_tram)
FROM tbl_prot_tramitacoes
WHERE id_setor_origem = 70
GROUP BY id_protocolo)

select within parentheses is used to obtain the id of lines with each first occurrence id_protocolo. Then, in the top select, these ids are used to obtain additional information.