Select Distinct

1
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 pdf field of tbl_prot_traps (where there is id_protocolo repetition) and id_setor_origem = 70 and need tb of the protocol and subject field of tbl_protocols (where there is NO repetition of id_protocolo v)

Now, how do I bring these records?

id_tram | protocolo | assunto | pdf

1          ra001    vazamentos  001.pdf

3          ra002    incendios   002.pdf

4          ra003    inundacoes  003.pdf
    
asked by anonymous 31.05.2015 / 00:16

1 answer

1

You can use a subquery to select the rows 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)

The select within the parentheses is used to get the id of the rows with the first occurrence of each id_protocolo . Then in the select from above, these ids are used to get the additional information.

    
31.05.2015 / 07:00