How do you combine the input of three parameters into one? I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs.



Here is the query:

SELECT    
  Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project,
  Test_Matrix.tblTestMatrix.Request,
  Test_Matrix.tblTestMatrix.Release,
  Test_Matrix.tblTestMatrix.TestName,
  Test_Matrix.tblTestMatrix.Formulation,
  Test_Matrix.tblTestMatrix.Container,
  Test_Matrix.tblTestMatrix.Closure,
  Test_Matrix.tblTestMatrix.Shipper,
  Test_Matrix.tblTestMatrix.Label_Front,
  Test_Matrix.tblTestMatrix.Label_Back,
  Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other,Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM
Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix
  ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber
  AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request
  AND Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE  (Test_Matrix.tblTestMatrix.Formulation = 'N5200-500') OR
       (Test_Matrix.tblTestMatrix.Container = '') OR
       (Test_Matrix.tblTestMatrix.Closure = '') OR
       (Test_Matrix.tblTestMatrix.Shipper = '') OR
       (Test_Matrix.tblTestMatrix.Label_Front = '') OR
       (Test_Matrix.tblTestMatrix.Label_Back = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release


When I run this in SSMS it returns data but when looking at it in the query designer I see that the three "other" components that I'm trying to combine into one field name "Other" does not appear to be correct.



I would appreciate some comments and suggestions on what I can do to combine these three input parameters into one output field.
I want this query, when the or case is selected and just one formulation N'9870-532' is selected to return only that formulation and of course the other components relating to that formulation.

Query:

SELECT 
Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release, Test_Matrix.tblTestMatrix.TestName, Test_Matrix.tblTestMatrix.Formulation, 
Test_Matrix.tblTestMatrix.Container, Test_Matrix.tblTestMatrix.Closure, Test_Matrix.tblTestMatrix.Shipper, Test_Matrix.tblTestMatrix.Label_Front, Test_Matrix.tblTestMatrix.Label_Back, 
Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other, Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request AND 
Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE (Test_Matrix.tblTestMatrix.Formulation = N'9870-532') OR
      (Test_Matrix.tblTestMatrix.Container = '') OR
      (Test_Matrix.tblTestMatrix.Closure = '') OR
      (Test_Matrix.tblTestMatrix.Shipper = '') OR
      (Test_Matrix.tblTestMatrix.Label_Front = '') OR
      (Test_Matrix.tblTestMatrix.Label_Back = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release


Results:


The return is not containing only Formulation 9870-532 and other associated items, seen above you have 9670-532 as a formulation item. 

Note: I answered instead of comment because a comment was too long.