Solved Issue Identifying No-Match Records

[pooldead]

I posted under a separate thread earlier, however had to delete due to accidentally posting some real user data.

My issue was that I was trying to compare two combinations of data, Table A.Job Code + Table A.Dept + Table A.Role = Table B.Job Code + Table B.Dept + Table B.Role. If Table A.Role did not exist in Table B, or any piece of the combination did not match, I needed that record from Table A inserted into Table C.

@theDBguy was able to provide me with the following query to resolve my issue (note: it does not include the INTO portion of SQL):

SELECT Table A.APP_USERNAME, Table A.Job_Code, Table A.Dept_ID, Table A.Roles
FROM Table A LEFT JOIN Table B ON (Table A.Dept_ID = Table B.DepartmentID) AND (Table A.Job_Code = Table B.[Job Code]) AND (Table A.Roles = Table B.Access_name)
WHERE (((Table B.[Job Code]) Is Null))
ORDER BY Table A.APP_USERNAME, Table A.Job_Code, Table A.Dept_ID, Table A.Roles;

And if you are wondering about the characters in front of APP_USERNAME, apparently those are included in CSV files run from SQL Server which indicate blank space before the word. My SQL people have looked into it and can't find a problem, but that's why those are there.

 

[theDBguy]

Hi. Are you saying the above query actually gets you the data you wanted? Just curious...

 

[pooldead]

Yes it does, I just added INTO Table C between the SELECT and FROM lines. I did a manual check of the first 150 records found with the query and did not find a single discrepancy.

 

[theDBguy]

Yes it does, I just added INTO Table C between the SELECT and FROM lines. I did a manual check of the first 150 records found with the query and did not find a single discrepancy.

Okay. Congratulations! Yes, I was holding off on the INTO part until we got the correct records pulled before saving it. Good luck with your project.