title: How to get the result with a single cte?
body: <p>I have 4 queries, each one starts with a CTE:</p>
  
  <pre><code>with stg as
  (
      select columns
      from stage
      where conditions1
  )
  </code></pre>
  
  <p>After this the differences begin. In first case, I need to join with 2 tables on some conditions</p>
  
  <pre><code>select cols
  from stg
  inner join table1 on cond2
  inner join table2 on cond3
  where con4
  </code></pre>
  
  <p>In second. I need to join to table2, but on different conditions</p>
  
  <pre><code>select cols
  from stg
  inner join table2 on cond5
  where cond6
  </code></pre>
  
  <p>In the third, I simply need all columns that fit cond7</p>
  
  <pre><code>select cols
  from stg
  where cond7
  </code></pre>
  
  <p>And in the fourth, again different table and cond</p>
  
  <pre><code>select cols
  from stg
  left join table3 on cond8
  where cond9
  </code></pre>
  
  <p>The thing is, after every query I need to insert changed data back. It's basically</p>
  
  <pre><code>insert into table stage
      select *
      from query(1-4)
  </code></pre>
  
  <p>So I can't chain this CTEs. And after every insert the cte returns less rows. For example</p>
  
  <p>First time it will return 100 rows, second 85 and so on. What I want is way to use the CTE just once. At first I thought about changing all join to left join, adding them to the CTE itself and creating a flag of sorts</p>
  
  <pre><code>with stg as
  (
      select 
          cols,
          table1.col as flag1,
          table2.col as flag2,
          table3.col as flag3
      from 
          stage
      left join 
          table1 on cond
      left join 
          table2 on cond
      left join 
          table3 on cond
      where 
          many conditions
  )
  </code></pre>
  
  <p>But this turned very messy, very quickly and didn't even work right. Then I though about actually chaining it and then unioning 4 CTEs to get rid if doubles if they exist. But that too doesn't seem like a smart choice. Are there any other variants?</p>