Display Value from Sub Query in SELECT

Question

I need to display the value MIN(pa.DataAdjudicacao) in the SELECT. And how can I replace this Subquery for a Join? How can I do it? As far as I have searched I haven't found any way to do it. Please help.

SELECT p.id, p.referencia
FROM PCTSproposta p
WHERE p.ID in (SELECT pa.Proposta
            FROM PropostaAdjudicada pa
            WHERE pa.Proposta = p.ID
            GROUP BY pa.Proposta
            HAVING MIN(pa.DataAdjudicacao) >= '2020-10-01' And MIN(pa.DataAdjudicacao) <= '2020-10-31')

Answer 1

Your query is overcomplicated. You use subquery to filter the data by IN predicate, but select the same ID that you've passed into filter of subquery. So your IN looks like EXISTS. But if you need that min, you should use JOIN:

SELECT p.id,
  p.referencia,
  pa2.DataAdjudicacao
FROM PCTSproposta p
  JOIN (
    SELECT pa.Proposta,
      MIN(pa.DataAdjudicacao) as DataAdjudicacao
    FROM PropostaAdjudicada pa
    GROUP BY pa.Proposta
    HAVING MIN(pa.DataAdjudicacao) BETWEEN date '2020-10-01'AND date '2020-10-31'
  ) pa2
    on pa2.Proposta = p.ID

Depending on roles of the Id column (if it is primary key of PCTSproposta), data integrity and cardinality the subquery can be merged into main query:

SELECT p.id,
  p.referencia,
  MIN(pa.DataAdjudicacao) as DataAdjudicacao
FROM PCTSproposta p
  JOIN PropostaAdjudicada pa
    on pa2.Proposta = p.ID
GROUP BY p.id,
  p.referencia
HAVING MIN(pa.DataAdjudicacao) BETWEEN date '2020-10-01'AND date '2020-10-31'