I would like to "declare" what are effectively multiple TEMP tables using the WITH statement.
The query I am trying to execute is along the lines of:
WITH table_1 AS (
SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date
WITH table_2 AS (
SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date
SELECT * FROM table_1
WHERE date IN table_2
Per the other comments the second Common Table Expression [CTE] is preceded by a comma not a WITH statement so
WITH cte1 AS (SELECT...) , cte2 AS (SELECT...) SELECT * FROM cte1.....
In terms of your actual query this syntax should work in PostgreSql, Oracle, and sql-server, well the later typically you will proceed
WITH with a semicolon but that is because typically sql-server folkes (myself included) don't end previous statements which need to be ended prior to a CTE being defined...
Note however that you had a second syntax issue in regards to your
WHERE date IN table_2 is not valid because you never actually reference a value/column from table_2. I prefer
INNER JOIN over
Exists so here is a syntax that should work with a
WITH table_1 AS ( SELECT GENERATE_SERIES('2012-06-29', '2012-07-03', '1 day'::INTERVAL) AS date ) , table_2 AS ( SELECT GENERATE_SERIES('2012-06-30', '2012-07-13', '1 day'::INTERVAL) AS date ) SELECT * FROM table_1 t1 INNER JOIN table_2 t2 ON t1.date = t2.date ;
If you want to keep the way you had it which typically EXISTS would be better than IN but to to use IN you need an actual SELECT statement in your where.
SELECT * FROM table_1 t1 WHERE t1.date IN (SELECT date FROM table_2);
IN is very problematic with
date could potentially be
NULL so if you don't want to use a
JOIN then I would suggest exists. AS follows:
SELECT * FROM table_1 t1 WHERE EXISTS (SELECT * FROM table_2 t2 WHERE t2.date = t1.date);