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.

Answer 1

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 |