May 21, 2009 at 3:15 pm
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_Type
FROM wce_ilr LEFT OUTER JOIN
wce_contact AS wce_contact ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_default
WHERE (wce_contact.EDRS IS NULL)
GROUP BY wce_ilr.EDRS
ORDER BY EDRS
May 22, 2009 at 10:37 am
I'm not sure that i really understand your question, but if your question is:
"How do I remove the row with a NULL value for wce_ilr.EDRS?", here is how I'd do it:
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_Type
FROM
wce_ilr LEFT OUTER JOIN
wce_contact AS wce_contact
ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_default
WHERE
-- added this line
wce_ilr.EDRS IS NOT NULL AND
(wce_contact.EDRS IS NULL)
GROUP BY
wce_ilr.EDRS
ORDER BY
EDRS
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply