I have a simple query running against MySQL through PHP. I want to insert into a table all values that are in another table but not in that table. This is the query I have:
INSERT Into DataNew (CollectorCode,CollectGross)
SELECT DISTINCT DataHistory.CollectorCode, 0
LEFT OUTER JOIN DataNew ON DataNew.CollectorCode = DataHistory.CollectorCode
WHERE DataHistory.CodeType =1
AND DataHistory.CollectorCode NOT LIKE '%-b'
AND DataNew.CollectorCode IS NULL
The trouble is that the select query returns 602 rows whether I fileter by the DataNew.CollectorCode is Null or not. How can I go about insertings into DataNew all Collectorcodes from DataHistory which are not already present?