Syntax error when using except in a query

by

I don’t believe MySQL supports EXCEPT syntax. Try using NOT IN or a LEFT JOIN:

SELECT s.sno 
FROM students s    
WHERE s.sno NOT IN 
(
    SELECT t.sno 
    FROM take t 
    WHERE t.cno = 'CS112'
);

OR 

SELECT s.sno 
FROM students s    
    LEFT JOIN take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112'

UPDATE

I mocked up your data as such and it correctly returns 5 through 10:

create temporary table temp_students (sno int)

insert into temp_students values (1)
insert into temp_students values (2)
insert into temp_students values (3)
insert into temp_students values (4)
insert into temp_students values (5)
insert into temp_students values (6)
insert into temp_students values (7)
insert into temp_students values (8)
insert into temp_students values (9)
insert into temp_students values (10)

create temporary table temp_take (sno int, cno varchar(50))

insert into temp_take values (1, 'CS112')
insert into temp_take values (2, 'CS112')
insert into temp_take values (3, 'CS112')
insert into temp_take values (4, 'CS112')

SELECT s.sno 
FROM temp_students s    
        LEFT JOIN temp_take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112'

More Related Contents:

  1. Recreating a query using INNER JOIN? [duplicate]
  2. What is better subquery vs literal on IN clause in mysql
  3. SQL query return data from multiple tables
  4. quick selection of a random row from a large table in mysql
  5. MySQL SELECT only not null values
  6. Ordering by specific field value first
  7. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  8. Checking multiple columns for one value
  9. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  10. MySQL INSERT INTO … VALUES and SELECT
  11. MySQL error 1241: Operand should contain 1 column(s)
  12. How to select one row randomly taking into account a weight?
  13. quick selection of a random row from a large table in mysql
  14. Compare only day and month with date field in mysql
  15. SELECT query return 1 row from each group
  16. MySQL INNER JOIN select only one row from second table
  17. MySQL Select Query – Get only first 10 characters of a value
  18. SQL query to check if a name begins and ends with a vowel
  19. How to SELECT by MAX(date)?
  20. SQL Query needed for fetch value from Two identical table but not fully identical table
  21. How to reset AUTO_INCREMENT in MySQL
  22. MySQL Query GROUP BY day / month / year
  23. SQL counting all rows instead of counting individual rows
  24. What is MySQL row order for “SELECT * FROM table_name;”?
  25. MySQL Like multiple values
  26. MySQL add days to a date
  27. Check for x consecutive days – given timestamps in database
  28. MySQL query, MAX() + GROUP BY
  29. ALTER table – adding AUTOINCREMENT in MySQL
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment