0

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 :-)

Improve this question
2
  • I mean that QUERY 2 that populates the list of PeopleID i want, in some way should be able to work together with QUERY 1 in ONE SINGLE QUERY - merge is maybe not the best word, but I don´t know how to put it actually - I hope this cleared it out for you. Commented May 21, 2010 at 11:49
  • I've updated my answer. Getting warmer? Commented May 21, 2010 at 12:16

2 Answers 2

Reset to default
1

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

3 Comments

I thought that as well. I'm still not really sure of the requirement having read the question about 3 times though! I can't see any dynamic column requirement but am probably missing something.
SELECT * is a bad practice to use. it will waste resources returning column that are not used, and it prevents the use of a covering index. @Martain Smith, I too am not really sure what the OP means by 'merge', what should the result set be? how to filter? etc.
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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.