Hi,I am using this query to select query that pulls only one row of data per employer, each employer should have a unique edrs number unfortunatly 500 records don't and won't for a little while.I am cross referenceing the wce_ilr and wce_contact tables, finding any records in the wce_ilr table that do not exist in the wce_contact table and then trying to insert only one instance of those that don't exist into the wce_contact table.The problem i am having is: i use the edrs as the uniqueid in the wce_contact table and this select part of the query brings, as it is being asked, one instance of a record where the unqiue value in the edrs field is the same, so it looks at the 500 records without an edrs and selects one but I can't insert a null value as a uniqueid... My Question is:can i then add some code to remove the null edrs row and just insert the values where the edrs exists? but in case i wasn't clear in the wce_ilr table the edrs numbers will exist many times.is because records do not have an edrs the below query is displaying one record that has a blank edrs.Thanks for any help.SELECT wce_ilr.EDRS AS Uniqueid, MAX(wce_ilr.EDRS) AS EDRS, MAX(wce_ilr.Employer_Name) AS Employer_name, MAX(wce_ilr.Emp_Phone) AS emp_phone, MAX(wce_ilr.Emp_Address_1) AS Address1, MAX(wce_ilr.Emp_Address_2) AS Address2, MAX(wce_ilr.Emp_Address_3) AS Address3, MAX(wce_ilr.Emp_Address_4) AS Address4, MAX(wce_ilr.Emp_Address_Pcode1) + ' ' + MAX(wce_ilr.Emp_Address_Pcode2) AS Postcode, 'Company' AS Record_TypeFROM wce_ilr LEFT OUTER JOIN wce_contact AS wce_contact ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_defaultWHERE (wce_contact.EDRS IS NULL)GROUP BY wce_ilr.EDRSORDER BY EDRS
INSERT - SELECT QUERY
Posted - 2009-05-21 : 06:35:28
