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
I've read PostgreSQL documentation and researched into using multiple WITH
statements and was unable to find an answer.
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 c1
INNER JOIN cte2 c2
ON ........
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 (;WTIH
), but that is because typically sql-server folks (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
statement. WHERE date IN table_2
is not valid because you never actually reference a value/column from table_2. I prefer INNER JOIN
over IN
or Exists
so here is a syntax that should work with a JOIN
:
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 when 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);