title: How do I get rid of these subselects?
body: <p>I have this query:</p>
  
  <pre><code>select distinct pdi.height, pdi.width, pj.jobnum
          , (select count(barcode) from productiondoorinformation 
              inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
                  where view_productionjobs.jobnum=pj.jobnum 
                      and productiondoorinformation.height=pdi.height 
                      and productiondoorinformation.width=pdi.width
                      and productiondoorinformation.alternaterating='PARTICLE') as particleqty
          , (select count(barcode) from productiondoorinformation 
              inner join view_productionjobs on view_productionjobs.ctr=productiondoorinformation.productionjobsctr
                  where view_productionjobs.jobnum=pj.jobnum 
                      and productiondoorinformation.height=pdi.height 
                      and productiondoorinformation.width=pdi.width
                      and productiondoorinformation.alternaterating&lt;&gt;'PARTICLE') as laminatedqty
  
      from productiondoorinformation pdi inner join view_productionjobs pj on pj.ctr=pdi.productionjobsctr
                  where pj.jobnum='' + @Jobnum + ''
  </code></pre>
  
  <p>There has to be a better way to be a better way to do this. I hate the subselects but am not savy enough to re-write it without them. There are two different quantities that the subselects are counting that you'll notice when you see the where clauses for each.</p>
  
  <p>This query takes nine seconds to execute. That's too long. Any other query I've written against these tables returns immediately. Any suggestions? I've tried group by, etc., but can only get it to work one way or the other (particle or &lt;> particle), but not both.</p>
  
  <p>The returned data should look like:  </p>
  
  <pre><code>height | width | jobnum | particleqty | laminatedqty 
  79      49       t1000    10           5
  78      49       t1000    1            3
  79      47       t1000    15           0
  </code></pre>