Validation, Filter results in SQL

Navigation
2

Good morning, I have a query that returns values of several functions and I need to filter it with another table B since I do not want it to show the record if it exists in table B This is the query I want to filter 

 SELECT FormaPago, NDCH, CORE, FechaPago, MontoReclamo,
 MontoIndemnizado, ImpuestoRetenido, ProntoPago, TotalPago, Deposito,
 FDeposito, NBanco, MIVA
 FROM dbo.fn_getPagosAgrupadosPrev('1', '1', '0001')
 ORDER BY FechaPago DESC

This is the result of this query 

FormaPago   NDCH    CORE    FechaPago   MontoReclamo    MontoIndemnizado    ImpuestoRetenido    ProntoPago  TotalPago   Deposito    FDeposito   NBanco  MIVA 
CHEQUE  47900005279 13257   2018-07-23  337.60           337.60               -16.88                0.00      320.72    0          00/00/0000   NULL     0.00
CHEQUE  47900019987 1323    2017-04-10  4703.87          4303.87             -154.59                  0.00       4149.28    0          00/00/0000   NULL    0.00
TRANSF  3751       13796162 2016-11-18  2410876.04      1200000.00          -48489.44             0.00      1151510.56  0         00/00/0000    NULL    0.00
TRANSF  3751       13801093 2016-11-18  186659.16       6659.16                  0.00                 0.00        6659.16   0         00/00/0000    NULL    0.00

and I need to tell you that if the CORE exists in table B that does not show the results 

select * from hd3928 WHERE CORE28 = 1323

I tried using an INNER JOIN but it does not work properly 

SELECT FormaPago, NDCH, CORE, FechaPago, MontoReclamo, MontoIndemnizado, ImpuestoRetenido, ProntoPago, TotalPago, Deposito, FDeposito, NBanco,  
MIVA
FROM dbo.fn_getPagosAgrupadosPrev('1', '1', '0001') 
INNER JOIN hd3928
ON CORE != CORE28 
ORDER BY FechaPago DESC

The result this brings is 

FormaPago   NDCH    CORE    FechaPago   MontoReclamo    MontoIndemnizado    ImpuestoRetenido    ProntoPago  TotalPago   Deposito    FDeposito   NBanco  MIVA 
CHEQUE  47900005279 13257   2018-07-23  337.60           337.60               -16.88                0.00      320.72    0          00/00/0000   NULL     0.00
CHEQUE  47900005279 13257   2018-07-23  337.60           337.60               -16.88                0.00      320.72    0          00/00/0000   NULL     0.00
CHEQUE  47900019987 1323    2017-04-10  4703.87          4303.87             -154.59                  0.00       4149.28    0          00/00/0000   NULL    0.00
CHEQUE  47900019987 1323    2017-04-10  4703.87          4303.87             -154.59                  0.00       4149.28    0          00/00/0000   NULL    0.00

and the desired result is to remove the record where CORE = 1323 

FormaPago   NDCH    CORE    FechaPago   MontoReclamo    MontoIndemnizado    ImpuestoRetenido    ProntoPago  TotalPago   Deposito    FDeposito   NBanco  MIVA 
CHEQUE  47900005279 13257   2018-07-23  337.60           337.60               -16.88                0.00      320.72    0          00/00/0000   NULL     0.00
TRANSF  3751       13796162 2016-11-18  2410876.04      1200000.00          -48489.44             0.00      1151510.56  0         00/00/0000    NULL    0.00
TRANSF  3751       13801093 2016-11-18  186659.16       6659.16                  0.00                 0.00        6659.16   0         00/00/0000    NULL    0.00

Is there another way I could do it?

    
asked by Hans 24.11.2016 в 16:09
source

3 answers

3

Another option 

SELECT FormaPago, NDCH, CORE, FechaPago, MontoReclamo,
 MontoIndemnizado, ImpuestoRetenido, ProntoPago, TotalPago, Deposito,
 FDeposito, NBanco, MIVA
FROM dbo.fn_getPagosAgrupadosPrev('1', '1', '0001') t
 where not exists(select top 1 null from hd3928 WHERE CORE28 = t.CORE)
ORDER BY FechaPago DESC
    
answered by 24.11.2016 / 16:21
source
2

You could use the WHERE clause, filtering only those that do not have a related record in the IVRDB2.S103V5HM.UNHC02.hd3928 table. For example: 

SELECT FormaPago, NDCH, CORE, FechaPago, MontoReclamo, MontoIndemnizado,
  ImpuestoRetenido, ProntoPago, TotalPago, Deposito, FDeposito, NBanco,
  MIVA
FROM dbo.fn_getPagosAgrupadosPrev('1', '1', '0001') 
WHERE CORE NOT IN (SELECT CORE28 FROM IVRDB2.S103V5HM.UNHC02.hd3928)
ORDER BY FechaPago DESC
    
answered by 24.11.2016 в 16:21
0

You should use a Left join instead of InnerJoin 

SELECT FormaPago, NDCH, CORE, FechaPago, MontoReclamo, MontoIndemnizado, ImpuestoRetenido, ProntoPago, TotalPago, Deposito, FDeposito, NBanco,  
MIVA
FROM dbo.fn_getPagosAgrupadosPrev('1', '1', '0001') 
**LEFT** JOIN IVRDB2.S103V5HM.UNHC02.hd3928
ON CORE != CORE28 
ORDER BY FechaPago DESC
    
answered by 24.11.2016 в 16:19
Calculate time between two dates: hours, minutes and seconds Regular php expression between tags