How do I filter one of the columns in a SQL Server SQL Query

Question

I have a table (that relates to a number of other tables) where I would like to filter ONE of the columns (RequesterID) - that column will be a combobox where only people that are not sales people should be selectable.

Here is the "unfiltered" query, lets call it QUERY 1:

SELECT RequestsID, RequesterID, ProductsID
FROM dbo.Requests

If using a separate query, lets call it QUERY 2, to filter RequesterID (which is a People related column, connected to People.PeopleID), it would look like this:

SELECT     People.PeopleID
FROM         People INNER JOIN
                  Roles ON People.RolesID = Roles.RolesID INNER JOIN
                  Requests ON People.PeopleID = Requests.RequesterID
WHERE     (Roles.Role <> N'SalesGuy')
ORDER BY Requests.RequestsID

Now, is there a way of "merging" the QUERY 2 into QUERY 1?

(dbo.Requests in QUERY 1 has RequesterID populated as a Foreign Key from dbo.People, so no problem there... The connections are all right, just not know how to write the SQL query!)

UPDATE

Trying to explain what I mean in a bit more... :

The result set should be a number of REQUESTS - and the number of REQUESTS should not be limited by QUERY 2. QUERY 2:s only function is to limit the selectable subset in column Requests.RequesterID - and no, it´s not that clear, but in the C# VS2008 implementation I use Requests.RequesterID to eventually populate a ComboBox with [Full name], which is another column in the People table - and in that column I don´t want SalesGuy to show up as possible to select; here I´m trying to clear it out EVEN MORE... (but with wrong syntax, of course)

SELECT RequestsID, (RequesterID WHERE RequesterID != 8), ProductsID
FROM dbo.Requests

Yes, RequesterID 8 happens to be the SalesGuy :-)

Answer 1

Following clarification. Something like this?

SELECT RequestsID, RequesterID, ProductsID, 
CAST(CASE WHEN Roles.Role IS NULL THEN 0 ELSE 1 END AS BIT) AS Selectable
FROM dbo.Requests
LEFT OUTER JOIN 
    People ON People.PeopleID = Requests.RequesterID
LEFT OUTER JOIN 
    Roles ON People.RolesID = Roles.RolesID AND  (Roles.Role <> N'SalesGuy')

Answer 2

here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.

here is the table of contents:

  Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

if you are on the proper version of SQL Server 2008, there is an additional technique that can be used, see: Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

If you are on that proper release of SQL Server 2008, you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

and an index can be used (if you have one defined on Column2)

Answer 3

How about this? Since the query already joins on the requests table you can simply add the columns to the select-list like so :

SELECT     Requests.RequestsID, Requests.RequesterID, Requests.ProductsID
FROM         People INNER JOIN
                  Roles ON People.RolesID = Roles.RolesID INNER JOIN
                  Requests ON People.PeopleID = Requests.RequesterID
WHERE     (Roles.Role <> N'SalesGuy')
ORDER BY Requests.RequestsID

You can in fact select any column from any of the joined tables (Roles, Requests, People, etc.)

It becomes clear if you just replace People.PeopleId with * and it will show you everything retrieved from the tables.