CTE wrong syntax

Question

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 ?

Your tag says "tsql" (imples Microsoft SQL Server) but your error says "MySQL server" which I guess is behind this web site. I guess MySQL doesn't support CTEs. Hint: EXISTS is your friend — gbn, Jun 17 '11 at 09:57
Irrespective to CTE , having will give you same issue in any RDBMS — Ash, Jun 17 '11 at 10:07
No CTE in MySQL http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql — Mikael Eriksson, Jun 17 '11 at 10:08
I've retagged your question. If something's wrong, you can fix it by clicking the `edit` link under the post. — Andriy M, Jun 17 '11 at 13:26

score 1 · Accepted Answer · answered Jun 17 '11 at 10:05

1

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

answered Jun 17 '11 at 10:05

Ash

2,531
2
29
38

so I can conclude: When grouping – always use agg in having and in select – Elad Benda Jun 17 '11 at 10:14
1

Please, CTE query do not need to start with a semi-colon. its the previous statement that need to end with a semi-colon. – Pierre-Alain Vigeant Jun 17 '11 at 13:56
@Pierre-Alain: the problem with your assertion is that SQL statements are not *required* to end with a semi-colon. So, in the interest of defensive coding, people sometimes put them at the start of statements that need to be delimited from the previous statement. This is especially true of places like this where you don't know where the code will be pasted. I think it better to nip the inevitable question of "why does my parser say that I need a semi-colon" in the bud by putting it at the front. – Ben Thul Jun 17 '11 at 15:29

score 0 · Answer 2 · answered Jun 17 '11 at 09:57

0

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')

answered Jun 17 '11 at 09:57

Elad Benda

35,076
87
265
471

Your original CTE solution would have been correct in SQL Server. But MySQL does not support CTEs so a different solution is required. – Anthony Faull Jun 17 '11 at 15:46

score 0 · Answer 3 · answered Jun 17 '11 at 13:31

0

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

answered Jun 17 '11 at 13:31

Andriy M

76,112
17
94
154

CTE wrong syntax

3 Answers3