Consult related data without repetition

Question

I need to list all data in the CONTRACT table, but only when in the COMMITMENT table blocker = 1:

CONTRATO   COMPROMISSO
codigos    codgerado blocker
3315       1102         2
3317       1105         1
3318       1108         1
3320       3315         2
7726       3317         1
           3318         1
           3320         1
           7726         1
           8801         2
           8802         2
           9907         2

I’m trying like this: (unsuccessfully)

SELECT * FROM contrato LEFT JOIN compromisso ON compromisso.codGerado = contrato.codigos WHERE compromisso.blocker = '1';

In the CONTRACT table there will only be codes that come from the COMMITMENT table, but the COMMITMENT table is much fuller and also only in the COMMITMENT table has the column blocker which will serve to say whether a code was "unused" or not.

How can I list the data in the CONTRACT table only with the codes that exist in it and in the COMMITMENT table, but only where blocker = 1?

Thank you

Answer 1

You can use the inner join even, if you want to analyze the contracts.

SELECT *
FROM
  contrato INNER JOIN compromisso ON compromisso.codGerado = contrato.codigos 
WHERE
  compromisso.blocker = '1';

This way, it will bring only when it exists in the CONTRACT table and that it exists in the COMMITMENT table and that the Blocker is 1. Commitments that have no contract do not bring.

Answer 2

If you are concerned about repeating the CONTRACT lines, do so as follows:

SELECT DISTINCT C.codigos, C.xyz, C.abc /*etc., com os campos de CONTRATO*/
FROM contrato C
LEFT JOIN compromisso CP ON C.codigos = CP.codgerado
WHERE CP.blocker = 1;