CTE wrong syntax

I'm trying to write a sql query to the following (from this site http://sqlzoo.net/1b.htm)

In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

1st try:

with c as
(select yr, subject
from nobel
where subject <> 'Chemistry')

select yr
from c开发者_开发知识库
group by yr
having c.subject ='Physics'

But I get a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'with c as (select yr, subject from nobel where subject <> 'Chemistry' at line 1

what's wrong?

2nd try:

select o.yr
from 
(select yr, subject
from nobel
where subject <> 'Chemistry') o
group by o.yr
having o.subject ='Physics'

But I get a syntax error:

Unknown column 'o.subject' in 'having clause'

what's wrong?

3rd try: how do I do this with JOIN ?

Problem in your first query is having clause. You can only use an aggregation over a column here

so these will work

;with c as 
(select yr, subject from nobel where subject <> 'Chemistry')  
select yr,count(c.subject) from c where c.subject ='Physics'  group by yr 

;with c as 
(select yr, subject from nobel where subject <> 'Chemistry')  
select yr,count(c.subject) from c group by yr  having count(c.subject) =1

same issue with second one

Having in T- SQL

This is one right answer, but I would still like to understand my mistakes.

   select distinct yr
    from nobel
    where subject = 'Physics'
    and yr not in (select yr from nobel where subject = 'Chemistry')

You could achieve the same result without subselects. This is how it could be implemented in MySQL:

SELECT yr
FROM nobel
GROUP BY yr
HAVING COUNT(subject = 'Chemistry' OR NULL) = 0
   AND COUNT(subject ='Physics' OR NULL) = 1