title: How to optimize SELECT statement with multiple sub-queries
body: <p>I have a query that I'm trying to optimize but haven't had much success. There are two tables, one with the main data and one with timestamps of when specific events occurred. The tables are relational using a common key of adID. I am trying to perform a query that pulls in all of the timestamps and other data from the main table. I have it working but I am trying to optimize so it runs faster.</p>
  
  <pre><code>SELECT a.ID,a.repID,a.artistID,
  (
      SELECT TOP 1 c.timestamp
      FROM Tracking AS c
      WHERE statusID = 4
      AND c.ID = a.ID
      ORDER BY c.timestamp ASC
  
  ) 
  AS created,
  (
      SELECT TOP 1 d.timestamp
      FROM Tracking AS d
      WHERE statusID = 5
      AND d.ID = a.ID
      ORDER BY d.timestamp ASC
  )
  AS claimed,
  (
      SELECT TOP 1 p.timestamp
      FROM Tracking AS p
      WHERE statusID = 6
      AND p.ID = a.ID
      ORDER BY p.timestamp ASC
  )
  AS proof,
  (
      SELECT TOP 1 v.timestamp
      FROM Tracking AS v
      WHERE statusID = 8
      AND v.ID = a.ID
      ORDER BY v.timestamp ASC
  )
  AS approved,
  (
      SELECT count(ID)
      FROM Tracking AS t
      WHERE statusID = 6
      AND t.ID = a.ID
  )
  AS proofcount
  FROM Advertising AS a
  WHERE a.statusID = 8
  </code></pre>
  
  <p>Any help on this is appreciated. I'm not too familiar with SQL Server so I am not too well versed in optimizing queries such as these.</p>