Use multiple WITH tablename AS (…) statements using Postgres version 9.3

Question

There is an exact question like this for SQL server but I'm using Postgres 9.3 and the ',' didn't work

I am making a comparison between 2 separate years. e.g. 2016 and 2015

I check the user if he received any bad grades with same subjects over a course of 2 years.

My code goes as follows

with currentyeargrade ( 
      select ....
      from (...
             ...)t)

with previousyeargrade(
      select ....
      from (...
             ...)y)

select *
from currentyeargrade cyg
inner join previousyeargrade pvg on pvg.userid = cyg.userid

I am an SQL developer and I do not have admin privileges to create a temp table.

D-Shih · Accepted Answer · 2018-08-28T08:15:29.833

7

When you use CTE you only need to write with in the first CTE and use , comma to connect multiple CTE.

with currentyeargrade as ( 
      select ....
      from (...
             ...)t),
previousyeargrade as(
      select ....
      from (...
             ...)y)
select *
from currentyeargrade cyg
inner join previousyeargrade pvg on pvg.userid = cyg.userid

Here is a sample for you

CREATE TABLE T(col int);

insert into T values (1);

Query 1:

with currentyeargrade as  (SELECT * FROM T), 
previousyeargrade as (SELECT * FROM T)
select *
from previousyeargrade cyg
CROSS JOIN currentyeargrade pvg

Results:

| col | col |
|-----|-----|
|   1 |   1 |

edited Aug 28 '18 at 08:15

answered Aug 28 '18 at 08:11

D-Shih

44,943
6
31
51

the difference was you removed the with on the second line, trying that now! – Tito Aug 28 '18 at 08:14
Awesome. Perfect Answer! Slight difference between SQL Server but removing the with worked! Thanks – Tito Aug 28 '18 at 08:16
@Tito No problem glad to help – D-Shih Aug 28 '18 at 08:22

Use multiple WITH tablename AS (…) statements using Postgres version 9.3

1 Answers1