NOT LIKE clause not working in SQL

Question

I am trying to list all the courses for a person, where the course code doesn't start with a 'C'. The following code is still bring up 'C-...' codes. Any idea how to fix it?

SELECT u.idnumber, u.firstname, u.lastname, r.id, c.idnumber AS m_name, c.id AS c_id
FROM mdl_user u
  LEFT JOIN mdl_role_assignments r ON u.id = r.userid
  LEFT JOIN mdl_context c1 ON r.contextid = c1.id
  LEFT JOIN mdl_course c ON c1.instanceid = c.id
WHERE u.idnumber = 'a.smoth' AND NOT c.id LIKE 'C%'

score 3 · Accepted Answer · answered Jun 26 '13 at 08:43

3

Just put the NOTjust before the LIKE:

... c.id NOT LIKE 'C%'

answered Jun 26 '13 at 08:43

xlecoustillier

16,183
14
60
85

score 2 · Answer 2 · answered Jun 26 '13 at 08:44

You not like syntax is not correct use it:

SELECT u.idnumber,u.firstname,u.lastname,r.id,c.idnumber AS m_name,c.id AS c_id
       FROM mdl_user u
            LEFT JOIN mdl_role_assignments r ON u.id = r.userid
            LEFT JOIN mdl_context c1 ON r.contextid = c1.id
            LEFT JOIN mdl_course c ON c1.instanceid = c.id
            WHERE u.idnumber = 'a.smoth' AND c.id NOT LIKE 'C%'
                                                  ^^^

This is NOT LIKE clause.

score 0 · Answer 3 · answered Jun 26 '13 at 08:50

0

Try this :



WHERE u.idnumber = 'a.smoth' AND c.id NOT LIKE 'C%'

answered Jun 26 '13 at 08:50

Ahmed

452
3
7

score 0 · Answer 4 · answered Jun 26 '13 at 08:50

0

WHERE u.idnumber = 'a.smoth' AND c.id NOT LIKE 'C%'

answered Jun 26 '13 at 08:50

Aboelseoud

556
1
7
22

NOT LIKE clause not working in SQL

4 Answers4