title: Need help in my query scenario
body: <p>I need help in writing a query for the below data (SecondaryTable),</p>
  
  <pre><code>--------------------------------------------------------------------------------
  PrimaryID1     PrimaryID2     TableID     FlagField     ServiceID     FieldData
  --------------------------------------------------------------------------------
  PID11          PID21          1             1             6             ABC
  PID11          PID21          2             0             6             DEF
  PID11          PID21          3             1             7             ABC
  PID11          PID21          4             0             7             DEF 
  PID11          PID21          5             0             8             ABC
  PID11          PID21          6             0             8             DEF 
  PID11          PID21          7             1             9             ABC
  PID11          PID21          8             0             9             DEF 
  </code></pre>
  
  <p>I need to write a query to in which I am joining it with the table (MainTable) that has the granularity level of "PrimaryID1" and "PrimaryID2", the requirement is like this for the combination of "PrimaryID1" and "PrimaryID2" if the table has "FlagField" = 1 for any one of the "ServiceID" of (6,7,8,9) then that field needs to be marked as "1".</p>
  
  <p>This is how I have implmeneted now,</p>
  
  <pre><code>SELECT
        MT.PrimaryID1,
        MT.PrimaryID2,
        CASE WHEN (SELECT TOP 1 ST.FieldID
                   FROM SecondaryTable
                   WHERE ServiceID IN (6,7,8,9) AND 
                         ST.PrimaryID1 = MT.PrimaryID1 AND
                         ST.PrimaryID2 = MT.PrimaryID2) IS NULL
             THEN 0 
             ELSE 1
        END AS SecondaryTableFlag
  
  FROM  MainTable MT
  </code></pre>
  
  <p>I know that if the top 1 comes as '0' then I am screwed, I even thought of having an order by in the correlated query but that won't work (obviously!!). Need to know if there is any alternative method for this one.</p>
  
  <p>In this above case of data for "SecondaryTable" this should be my output, but I am not sure about my above query</p>
  
  <pre><code>-------------------------------------------------
  PrimaryID1     PrimaryID2    SecondaryTableFlag
  -------------------------------------------------
  PID11          PID21         1
  </code></pre>