title: What is alternative instead of view in MySQL?
body: <p>I have below query and it isn't very important what the result is</p>
  
  <pre><code>SELECT
      `doctors`.`id` AS `id`,
      `doctors`.`name` AS `name`,
      count( `onvan-naghs`.`negative-mark` ) AS `negative_marks` 
  FROM
      (
      (
      (
      ( `doctors` JOIN `doctor-barge-naghs` ON ( ( `doctor-barge-naghs`.`code-doctor` = `doctors`.`id` ) ) )
      JOIN `onvan-naghs` ON ( ( `doctor-barge-naghs`.`code-naghs` = `onvan-naghs`.`id` ) ) 
      )
      JOIN `barge` ON ( ( `doctor-barge-naghs`.`code-barge` = `barge`.`id` ) ) 
      )
      JOIN `paziresh` ON ( ( `barge`.`shomare-paziresh` = `paziresh`.`id` ) ) 
      ) 
  WHERE
      ( `paziresh`.`tarikh_paziresh` BETWEEN 1111111111 AND 9999999999 ) 
  GROUP BY
      `doctors`.`id`,
      `doctors`.`name` 
  ORDER BY
      `onvan-naghs`.`negative-mark` DESC
  </code></pre>
  
  <p>then I save it as a view that  called  <code>scores</code>
  then I use it in another query and it works well</p>
  
  <pre><code>SELECT
      `scores`.`id` AS `id`,
      `scores`.`name` AS `name`,
      `scores`.`negative_marks` AS `negative_marks`,
      find_in_set( `scores`.`negative_marks`, ( SELECT group_concat( DISTINCT `scores`.`negative_marks` ORDER BY `scores`.`negative_marks` DESC SEPARATOR ',' ) FROM `scores` ) ) AS `rank` 
  FROM
      `scores`
  </code></pre>
  
  <p>but my problem is that I couldn't use a view in some situation
  and I want to use an entire query and I have to use a sub-query
  but I couldn't use a sub query outside it
  like below</p>
  
  <pre><code>SELECT *,
      find_in_set( `t1`.`negative_marks`, ( SELECT group_concat( DISTINCT `t1`.`negative_marks` ORDER BY `t1`.`negative_marks` DESC SEPARATOR ',' ) FROM `t1` ) ) AS `rank` 
  FROM
  (
  SELECT
      `doctors`.`id` AS `id`,
      `doctors`.`name` AS `name`,
      count( `onvan-naghs`.`negative-mark` ) AS `negative_marks` 
  FROM
      (
      (
      (
      ( `doctors` JOIN `doctor-barge-naghs` ON ( ( `doctor-barge-naghs`.`code-doctor` = `doctors`.`id` ) ) )
      JOIN `onvan-naghs` ON ( ( `doctor-barge-naghs`.`code-naghs` = `onvan-naghs`.`id` ) ) 
      )
      JOIN `barge` ON ( ( `doctor-barge-naghs`.`code-barge` = `barge`.`id` ) ) 
      )
      JOIN `paziresh` ON ( ( `barge`.`shomare-paziresh` = `paziresh`.`id` ) ) 
      ) 
  WHERE
      ( `paziresh`.`tarikh_paziresh` BETWEEN 1111111111 AND 9999999999 ) 
  GROUP BY
      `doctors`.`id`,
      `doctors`.`name` 
  ORDER BY
      `onvan-naghs`.`negative-mark` DESC
      ) as t1
  </code></pre>
  
  <p>It couldn't use t1 and understand what it is and say 'Table 'client-hospital.t1' doesn't exist'</p>
  
  <p>What should I do  when I want to use an entire query instead of having  different views and sub-query couldn't be used outside?</p>
  
  <p>I have tried using with syntax but it isn't supported in my platform
  I need something  like below code</p>
  
  <pre><code>WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM table2)
  SELECT b, d FROM cte1 JOIN cte2
  WHERE cte1.a = cte2.c;
  </code></pre>
  
  <p>and for example, imagine below picture
  <a href="https://i.stack.imgur.com/B48EX.jpg" rel="nofollow noreferrer">an example</a>
  why I couldn't use a sub-query this way if I find a way for this I think a get my answer</p>