CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ChootsMagoots
ChootsMagoots

Reputation: 680

Use multiple WITH tablename AS (...) statements SQL Server

I'm trying to create two temporary tables and join them with a permanent table. For example: 

WITH temp1 AS (COUNT(*) AS count_sales, ID FROM table1 GROUP BY ID)
WITH temp2 AS (COUNT(*) AS count_somethingelse, ID FROM table2 GROUP BY ID)
SELECT *
FROM table3 JOIN table2 JOIN table1
     ON table1.ID = table2.ID = table3.ID

but there seems to be an issue having multiple WITH tablename AS (...) statments. I tried a semicolon.

Upvotes: 2

Views: 8567

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

I'm trying to create two temporary tables

Just for clarity... you used a CTE which is not quite the same thing as a temporary table. You've also tagged 'temp tables', so you want a temp table? You can store query results in a declared table variable or an actual temp table.

An example of declared table variables:

DECLARE @table1 TABLE(id int, count_sales int) 

INSERT INTO @table1 (id, count_sales)
SELECT ID, COUNT(*)
  FROM table1
GROUP BY ID
--or however you populate temp table1

DECLARE @table2 TABLE(id int, count_somethingelse int)

INSERT INTO @table2 (id, count_somethingelse)
SELECT ID, COUNT(*)
  FROM table2
GROUP BY ID
--or however you populate temp table2

SELECT T3.id
      --,T2.(some column)
      --,T1.(some column)
      --,etc...
FROM table3 T3 INNER JOIN @table2 T2 ON T3.id = T2.id
               INNER JOIN @table1 T1 ON T3.id = T1.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

Your query should look more like this:

WITH temp1 AS (
      SELECT COUNT(*) AS count_sales, ID
      FROM table1
      GROUP BY ID
     ),
     temp2 AS (
      SELECT COUNT(*) AS count_somethingelse, ID
      FROM table2
      GROUP BY ID
     )
SELECT *
FROM temp2 JOIN 
     temp1
     ON temp1.ID = temp2.ID;

Your query has multiple errors. I would suggest you start by understanding why this version works -- or at least does something other than report on syntax errors. Then, go back and study SQL some more.

Upvotes: 6

Related Questions