title: Getting Non matching row from mysql table using left join
body: <p>I have a steps table in my DB. I want to fetch all the steps to be executed next for each operation (each step is attached to a operation). Steps are executed in sequence according to field step_number (type: decimal). Conditions are as follows</p>
  
  <p>1- A step can be started if previous step of same operation is completed.</p>
  
  <p>2- Steps belonging to same operation and having same integer value for e.g. 1.1 and 1.2 can run concurrently</p>
  
  <p>3- If its first step of the operation then start it</p>
  
  <pre><code>SELECT 
          MIN(s1.step_number),
          s1.type,
          s1.operation,
          MAX(s2.step_number)
      FROM steps s1
      LEFT JOIN steps s2 
          ON  s1.operation = s2.operation
      WHERE 
          s1.state= 'not_started'
          AND s2.state != 'not_started'
          AND 
          (
              /* 1- A step can be started if previous step of same operation is completed. */
              (s2.state = 'confirmed')
              OR
              (
                /* 2- Steps belonging to same operation and having same integer value for e.g. 1.1 and 1.2 can run concurrently */
                  s2.state != 'confirmed'
                  AND CAST(s1.step_number AS UNSIGNED) = CAST(s2.step_number AS UNSIGNED)
              )
              OR
               /* 3- If its first step of the operation then start it */
              s1.step_number = (
                  SELECT
                          MIN(step_number)
                      FROM steps s3
                      WHERE operation = s1.operation
                  )
          )
      GROUP BY operation
  </code></pre>
  
  <p>The query is not returning rows matched by condition # 3 as these are already filtered out due to Left Join. Is there a alternate way to do this so I can get rows for all three conditions.</p>