Exclude records that are in another table in a [closed] query

Question

Exclude records that are in another table in a [closed] query

Navigation

#1 by (0 votes)

-1

select idConceptoGastos,rtrim(NombreConcepto) 
from Concepto_Gastos 
where not idConceptoGastos in (select IdConcepto from Reporte_Formulacion) and IdPartidaGastos = '1' and Vigente = '1'
and not NombreConcepto = 'ALCOHOL DE 96°'and NombreConcepto like 'ALCOHOL DE%'
or NombreConcepto like 'CONCENTRADO LICOR%'or NombreConcepto like 'CONCENTRADO REFRESCO%'
or NombreConcepto like 'CONCENTRADO SIDRA%'or NombreConcepto like 'CONCENTRADO VINO%' or NombreConcepto like 'PULPA%'
order by NombreConcepto

When I do my query in this way I do not exclude all of them, only 3 and they are alcohols and if I put the subquery to the last one only excludes me a pula, because I have added 5 records to the table of report formulation, 3 alcohols, a concentrate and a pulp, if I remove all of the likes if you correctly perform the query and exclude me the 5 records that are in the second table

sql server

asked by Javier Hernandez 09.04.2018 в 18:38

source

1 answer

validate asp.net text field? know percentage in javascript

score 0 · Accepted Answer

Try the query this way:

 select idConceptoGastos,rtrim(NombreConcepto) 
 from Concepto_Gastos 
 where idConceptoGastos not in (select IdConcepto from Reporte_Formulacion) 
 and IdPartidaGastos = '1' 
 and Vigente = '1' 
 and NombreConcepto <> 'ALCOHOL DE 96°'
 and (
        NombreConcepto like 'ALCOHOL DE%' 
        or NombreConcepto like 'CONCENTRADO LICOR%'
        or NombreConcepto like 'CONCENTRADO REFRESCO%' 
        or NombreConcepto like 'CONCENTRADO SIDRA%'
        or NombreConcepto like 'CONCENTRADO VINO%' 
        or NombreConcepto like 'PULPA%' 
     )
 order by NombreConcepto