title: SQl Query selecting 1 set of data over another
body: <p>I hope there's  enough information below for you to assist</p>
  
  <p>TABA</p>
  
  <p>ComapanyID TrimFields</p>
  
  <p>1111   |    3</p>
  
  <p>2222   |   1</p>
  
  <p>3333   |   2</p>
  
  <p>4444   |   4</p>
  
  <p>5555   |    2</p>
  
  <p>6666   |    3</p>
  
  <p>TABB</p>
  
  <p>SampleID | TestID | Microcomments  |ProductID  |ComapanyID</p>
  
  <p>111101  |  Test1  |Satisfactory    |ABC123     | 1111</p>
  
  <p>111101  | Test2   |No Test         |ABC123     | 1111</p>
  
  <p>111101  | Test3   |Unsatisfactory  |ABC123     | 1111</p>
  
  <p>222202  | Test2   |Satisfactory    |A21        | 2222</p>
  
  <p>222202  | Test3   |Unsatisfactory  |A21        | 2222</p>
  
  <p>333301  | Test1   |Satisfactory    |AB11       | 3333</p>
  
  <p>333301  | Test3   |No Test         |AB11       | 3333</p>
  
  <p>444403  | Test2   |Unsatisfactory  |ABCD123    | 4444</p>
  
  <p>444403  | Test3   |No Test         |ABCD123    | 4444</p>
  
  <p>555504  | Test1   |Unsatisfactory  |BA123      | 5555</p>
  
  <p>555504  | Test2   |Unsatisfactory  |BA123      | 5555</p>
  
  <p>666601  | Test3   |Satisfactory    |BBB21      | 6666</p>
  
  <p>666601  | Test2   |Satisfactory    |BBB21      | 6666</p>
  
  <hr>
  
  <p>At the moment my query is:
  declare @num int</p>
  
  <p>Set @companyID = @CompanyID</p>
  
  <p>select  @num = isnull(trimfields,0)
  from TABA where companyid = @companyID</p>
  
  <p>select distinct TABB.SampleID, TABB.MicroComments,<br>
          LTRIM(RIGHT(TABB.ProductID, len(rtrim(TABB.productID)) - @num)) as ProductID, </p>
  
  <p>where TABB.CompanyID = @CompanyID </p>
  
  <hr>
  
  <p>So when I run this Query for each CompanyID (WHERE CLAUSE) I get the following results</p>
  
  <p>SampleID  Microcomments   ProductID  </p>
  
  <p>111101    Satisfactory    123    </p>
  
  <p>111101    NoTest          123 </p>
  
  <p>111101    Unsatisfactory  123 </p>
  
  <p>222202    Satisfactory    21<br>
  222202    Unsatisfactory  21  </p>
  
  <p>333301    Satisfactory    11<br>
  333301    NoTest          11  </p>
  
  <p>444403    Unsatisfactory  123<br>
  444403    NoTest          123 </p>
  
  <p>555504    Unsatisfactory  123<br>
  555504    Unsatisfactory  123  </p>
  
  <p>666601    Satisfactory    21<br>
  666601    Satisfactory    21   </p>
  
  <hr>
  
  <p>I need it to output  to be summarised with the folowing logic;</p>
  
  <p>Logic 1- if the the sample id is the same but the Microcomment has Satis &amp; Unsat &amp; NoTest (EG. CompanyID 1111) 
  then only show 'Unsatifactory'</p>
  
  <p>Logic 2- if the the sample id is the same but the Microcomment has Satis &amp; Unsati (EG. CompanyID 2222) 
  then only show 'Unsatifactory'</p>
  
  <p>Logic 3-  the the sample id is the same but the Microcomment has Satis &amp; NoTest (EG.CompanyID 3333)
  Then only Show 'Satisfactory'</p>
  
  <p>Logic 4-  the the sample id is the same but the Microcomment has Unsat &amp; NoTest (EG.CompanyID 4444)
  Then only Show 'Unsatisfactory'</p>
  
  <p>Logic 5-  the the sample id is the same but the Microcomment is only Unsat (EG.CompanyID 5555)
  Then only Show 'Unsatisfactory'</p>
  
  <p>Logic 5-  the the sample id is the same but the Microcomment is only Satis (EG.CompanyID 6666)
  Then only Show 'Unsatisfactory'Then only Show 'Satisfactory'</p>
  
  <hr>
  
  <p>So that the output would then be;</p>
  
  <p>SampleID  Microcomments   ProductID<br>
  111101    Unsatisfactory  123 </p>
  
  <p>222202    Unsatisfactory  21  </p>
  
  <p>333301    Satisfactory    11       </p>
  
  <p>444403    Unsatisfactory  123    </p>
  
  <p>555504    Unsatisfactory  123      </p>
  
  <p>666601    Satisfactory    21       </p>
  
  <p>I’ve tried case statement, group by, Joins, MIN/MAX...just having no luck </p>