RE: SQL HELP FIXED
I put this where statment in and it fixed the problem. The emp_id_nr in tblemployee was null for empnr starting with six zeros. Josh DELETE FROM temptblJOBEVL WHERE (SYS_EMP_ID_NR NOT IN (SELECT emp_id_nr FROM tblemployee where emp_nr not like '00%')) -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 8:53 AM To: CF-Talk Subject: Re: SQL HELP Joshua Tipton wrote: For some unknown reason this query does not work correctly even though I know that there is data in temptbljobevl that does not match tblemployee data. Can someone please help me do this via a join. I doubt that rewriting as a join is going to solve anything. That would be a bug of a size that would be known, and it is not unlikely your database does that internally anyway. Maybe better to start looking if SYS_EMP_ID_NR and emp_id_nr don't have any NULL's, have the same datatype, are padded the same way or something. SELECT * FROM temptblJOBEVL WHERE (SYS_EMP_ID_NR NOT IN (SELECT emp_id_nr FROM tblemployee)) Rewriting that requires an OUTER JOIN, which might be less than efficient. But if you want to try: SELECT job.* FROMtemptblJOBEVL job LEFT JOIN tblemployee emp ON job.SYS_EMP_ID_NR = emp.emp_id_nr WHERE emp.emp_id_nr IS NULL I prefer the NOT EXISTS syntax, I think it is easier to read: SELECT job.* FROMtemptblJOBEVL job WHERE NOT EXISTS ( SELECT 1 FROMtblemployee emp WHERE job.SYS_EMP_ID_NR = emp.emp_id_nr ) All should return the same results, but have fun :) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
RE: SQL HELP FIXED
As I said - your syntax looked ok - however I wouldn't suggest that what you have is robust enough. If you are comparing 2 fields like this they ought to hold the same data. I would look at cleaning the data so that they don't contain the leading zero's or you add the leading zero's in the other table -Original Message- From: Joshua Tipton [mailto:[EMAIL PROTECTED]] Sent: 15 October 2002 14:11 To: CF-Talk Subject: RE: SQL HELP FIXED I put this where statment in and it fixed the problem. The emp_id_nr in tblemployee was null for empnr starting with six zeros. Josh DELETE FROM temptblJOBEVL WHERE (SYS_EMP_ID_NR NOT IN (SELECT emp_id_nr FROM tblemployee where emp_nr not like '00%')) -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 15, 2002 8:53 AM To: CF-Talk Subject: Re: SQL HELP Joshua Tipton wrote: For some unknown reason this query does not work correctly even though I know that there is data in temptbljobevl that does not match tblemployee data. Can someone please help me do this via a join. I doubt that rewriting as a join is going to solve anything. That would be a bug of a size that would be known, and it is not unlikely your database does that internally anyway. Maybe better to start looking if SYS_EMP_ID_NR and emp_id_nr don't have any NULL's, have the same datatype, are padded the same way or something. SELECT * FROM temptblJOBEVL WHERE (SYS_EMP_ID_NR NOT IN (SELECT emp_id_nr FROM tblemployee)) Rewriting that requires an OUTER JOIN, which might be less than efficient. But if you want to try: SELECT job.* FROMtemptblJOBEVL job LEFT JOIN tblemployee emp ON job.SYS_EMP_ID_NR = emp.emp_id_nr WHERE emp.emp_id_nr IS NULL I prefer the NOT EXISTS syntax, I think it is easier to read: SELECT job.* FROMtemptblJOBEVL job WHERE NOT EXISTS ( SELECT 1 FROMtblemployee emp WHERE job.SYS_EMP_ID_NR = emp.emp_id_nr ) All should return the same results, but have fun :) Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
