0

I have been plugging away at this and I am really at a loss for what I'm messing up. I am using My SQL workbench and sending queries to a mariadb. The original function I am working with, returns records just fine, however they pull in all of them (of which there are 1000+). Ideally the function would only return new records instead of all of them (hence no delete). I am trying to use WHERE NOT EXISTS however SQL is returning error code 1064. Sample code below:

SELECT WHERE NOT EXISTS 
usr1.email 
 , case when ccm.course = 7 then 'CourseComplete' 
  when ccm.course = 10 then 'Course1' when ccm.course = 4 then 'Course1CourseComplete' else 'other' end as coursecompleted 
 , course 
 ,  date_add(from_unixtime(timecompleted), INTERVAL 1 HOUR) as CompletionDate 
 FROM  moodledb.m_course_completions ccm 
 inner join m_user usr1 
 on ccm.userid = usr1.id 
 where timecompleted is not null and ccm.course in (4,7,10) order by 4 desc

In addition I've tried alternate placements for the where not exists function, but they all return the same error1064. Would a different operator work better? Can goku defeat this new foe? Any suggestions will be greatly appreciated

staticstickershock
  • 1
  • `EXISTS` and `NOT EXISTS` are used in subqueries. Where you are trying to use it does not appear to be a format that is valid or supported. What exactly are you trying to check for not existing? – Dave May 28 '20 at 12:20
  • remove `WHERE NOT EXISTS`, replace join with`LEFT join m_user usr1 on ccm.userid = usr1.id WHERE usr1 IS NULL AND ...` – danblack May 29 '20 at 00:31
  • @danblack Thank you! It looks like I was able to get around the 1064, i got an new error but I think ill be able to make this work. Again thank you! – staticstickershock May 30 '20 at 01:12
  • ops. obviously should be `...usr1.id IS NULL...` – danblack May 31 '20 at 01:21
  • Does this answer your question? [SELECT \* WHERE NOT EXISTS](https://stackoverflow.com/questions/915643/select-where-not-exists) – danblack May 31 '20 at 01:22

0 Answers0