Subquery with left Join

Question

I’m trying to search for records in a table where the product does not have certain characteristics. I would like to display only the records that do not have these characteristics, even if there are several others, I tried with the query below, it almost worked, rs. Thank you for your attention and collaboration.

select CODPROD
    from PRODUTOS
    left join (select CODPROD as CODPROD1, CARACT
                from PRODUTOS
                where DTFIM = '99991231'
                    and CARACT in ('X001','X002','X003')
                    and CODPROD <> ' '
                group by (CODPROD,CARACT))
     on CODPROD = CODPROD1
     group by CODPROD

Answer 1

Analyzing your case I believe that it is not necessary to use the Subquery, but I would recommend creating the code as follows:

SELECT CODPROD, CARACT 
FROM PRODUTOS
WHERE CODPROD NOT IN (SELECT DISTINCT CODPROD
                  FROM PRODUTOS 
                  WHERE DTFIM = '99991231' 
                  AND CARACT IN ('X001','X002','X003')
                  AND CODPROD <> ' ')
GROYP BY CODPROD, CARACT

I used the Subquery in a Where seeking only the necessary information from CODPROD.