title: For each unique key in a table do a select statement
body: <p>I have One table, containing both <code>operators</code> and <code>operatorgroups</code>.
  Operators are linked to 'operatorgroups' via a link table.
  Now, for each 'operatorgroup' I would like to select the operators linked to it.</p>
  
  <p>It needs to be dynamic, so when a new 'operatorgroup' is created I don't have to adjust the query.</p>
  
  <p>What I do now is repeat the following query to list the names of the operators and the 'operatorgroup' name, but I have to rewrite the query every time I add an 'operatorgroup'.
  How can I make the same query for all distinct 'operatorgroups'</p>
  
  <pre><code>SELECT Distinct A.[ref_dynanaam], A.[naam] 
  FROM [dbo].[actiedoor] A  
  LEFT JOIN [dbo].[actiedoorlink] B 
  ON B.[actiedoorid]=A.[unid] 
  OR B.[actiedoorgroepid] = A.[unid]
  WHERE B.[actiedoorgroepid] ='unid' AND status &gt;='1' 
  order by A.[naam] Desc
  </code></pre>
  
  <p>This gives me </p>
  
  <p><strong>ref_Dynanaam</strong>                <strong>Naam</strong><br>
  Makelaars   Makelaars<br>
  Eyk, Johan van<br>
  Hoetmer, JanR<br>
  Rooijen, Manon van  </p>
  
  <p>Note that only Coaches appears twice.
  I would like said list for all 'operatorgroups' (for each distinct b.actiedoorgroepid)<br>
  <strong>Screenshot 1</strong> 
  Cannot post screenshots due to to0 few reputation points<br>
  = a subsection from the 'actiedoor' and actiedoorlink tables<br>
  <strong>Screenshot 2</strong><br>
  Cannot post screenshots due to to0 few reputation points<br>
  = the result of the above mentioned query</p>
  
  <p>Another edit:<br>
  So, I'm one step further.<br>
  By executing the following query I get sort of what I need:  </p>
  
  <pre><code>SELECT Distinct A.[ref_dynanaam], A.[naam], B.[actiedoorgroepid] 
  FROM [dbo].[actiedoor] A 
  LEFT JOIN [dbo].[actiedoorlink] B 
  ON B.[actiedoorid]=A.[unid] 
  OR B.[actiedoorgroepid] = A.[unid] 
  WHERE (B.[actiedoorgroepid] 
  IN (Select DISTINCT B.[actiedoorgroepid]from actiedoorlink as B 
  WHERE B.[actiedoorgroepid] = B.[actiedoorgroepid])  AND A.[status] &gt;='1') 
  GROUP BY B.[actiedoorgroepid], A.[naam], A.[ref_dynanaam]  
  </code></pre>
  
  <p>This gives me: </p>
  
  <p><strong>ref_Dynanaam naam actiedoorgroepid</strong><br>
  Hoetmer, Jan        4e0001<br>
  Leurink, Rob        4e0001<br>
  Rooijen, Manon van  4e0001<br>
  Soodoo, Adjai       4e0001<br>
  Ideeenmanagement Ideeenmanagement 4e0001<br>
  Eyk, Johan van      4a0002<br>
  Hoetmer, Jan        4a0002<br>
  Rooijen, Manon van 4a0002<br>
  Makelaars Makelaars 4a0002  </p>
  
  <p>Now, for each row in the result set I need to execute another subquery.<br>
  Any suggestions?</p>