Getting an Odd Syntax Error With SQL Query

Question

I'm writing what should be a fairly straightforward SQL query for a modification of the IMDB database; it's supposed to return a list of all films that are categorized as BOTH horror and comedy, which I've done by creating a list of horror, a list of comedy, and then deleting everything from one that's not in the other. The query is as follows:

WITH

table_left AS (SELECT primary_names.name AS name, year, genre, title_id
FROM titles NATURAL JOIN primary_names NATURAL JOIN title_genres WHERE genre = 'Horror'),

table_right AS (SELECT primary_names.name AS name, year, genre, title_id
FROM titles NATURAL JOIN primary_names NATURAL JOIN title_genres WHERE genre = 'Comedy')

DELETE FROM table_right WHERE (title_id NOT IN (SELECT table_left.title_id))

SELECT name, year FROM table_right;

However, this generates an "ERROR: syntax error at or near 'SELECT'" on the last line of the query. I'm fairly new to SQL, but have gone over the syntax multiple times and checked some guides and I just can't understand what's going wrong. There shouldn't be a comma after the DELETE FROM statement, I don't think I've got a comma in any inappropriate places...it may be staring me in the face, but I'm at a loss, and would love to hear any suggestions.

Bill Karwin · Accepted Answer · 2018-03-01 04:45:27Z

2

When you use the WITH syntax, you can declare multiple table expressions, but then you can follow it with just one SQL query.

But you have two — a DELETE followed by a SELECT, with no statement terminator between the two statements. This doesn't match any syntax rule of SQL.

I could comment that another way to achieve what you want, listing films that are in two categories, is to do a self-join.

SELECT p.name, t.year, t.title_id
FROM titles AS t
INNER JOIN title_genres AS g1 ON t.title_id = g1.title_id AND g1.genre = 'Horror'
INNER JOIN title_genres AS g2 ON t.title_id = g2.title_id AND g2.genre = 'Comedy'
INNER JOIN primary_names AS p ON t.title_id = p.title_id

You really need to learn how to use JOIN if you're coding with SQL. Not doing so is like using another language like Java or Ruby without understanding loops. I don't mean joins are like loops, just that joins are a foundational part of the SQL language, and you need to know how to use them.

answered Mar 1, 2018 at 4:45

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MMMMMCK Over a year ago

Great answer, and you're right, I do definitely still need practice with the SQL joins. Thank you for the help!

Collectives™ on Stack Overflow

Getting an Odd Syntax Error With SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related