Re: SUBQUERY PROBLEM

[Odimar Tomazeli]

I finally found that the select you send me with LEFT JOIN is not working properly

SELECT e1.ReportID, e1.ReportName, e1.FromDate, e1.ToDate, e1.Market, e1.Entity, e1.Submarket, e1.CurrencyCode, e1.Customer, e1.GSTAmount, e1.HSTAmount, e1.BCAmount, e1.MBAmount, e1.ONAmount, e1.PEAmount, e1.QCAmount, e1.SKAmount, 'ADD', NOW()
FROM nm1_customer_exempt e1 LEFT OUTER JOIN nm1_customer_exempt e2 ON e1.Market = e2.Market
AND e1.Entity = e2.Entity AND e1.Submarket = e2.Submarket
AND e1.CurrencyCode = e2.CurrencyCode AND e1.Customer = e2.Customer
AND e2.FromDate >= '2005-11-01 00:00:00' AND e2.ToDate <= '2005-11-30 00:00:00'
WHERE e1.FromDate >= '2005-12-01 00:00:00'
AND e1.ToDate <= '2005-12-31 00:00:00'

The join return me around 14.000 records but should return me only around 1500 records because I’m trying to get only the records are in the table A and not in table B

Select A.* from A
WHERE CONCAT ( A.Field1,A.Field2,A.Field3) NOT IN( SELECT CONCAT(B.Field1,B.Field2,B.Field3) FROM B)

Best Regards,


Odimar Tomazeli