Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-18 : 07:11:43
|
| Nick writes "I am faced with an issue where a user can select from 10 drop down boxes. The user can select none, one or more values from each box. This criteria has to be used to fetch data from the database.The problem lies in the number of combinations that are possible. To simplify things, if user selects 2 values from Box 'A' and 2 values from Box 'B' and no value from Box 'C', I create 3 temporary tables #A, #B, #C to hold the set of values and my 'WHERE' clause looks like :where Value1 in (select * from #A) and Value2 in (select * from #B) and Value3 in (select * from #C).This causes no data to be displayed because #C has no data.My objective is to ignore #C in my select statement because the user has not selected it. With 10 selection criteria this has become a very complex task. Appreciate if you can suggest a way to address this." |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-04-18 : 07:43:31
|
| if you store the Count of the rows in your temporary tables #A, #B and #C you could do:where (@A_Count = 0 OR Value1 in (select * from #A))and (@B_Count = 0 OR Value2 in (select * from #B))and (@C_Count = 0 OR Value3 in (select * from #C))Kristen |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-04-18 : 08:52:40
|
Using the same basic idea, you might have better performance with something like this:select A.*from yourdata Aleft outer join #a on ...left outer join #b on ...left outer join #c on ...where (@A_Count = 0 or #a.SomePKColumn is not null) AND (@B_Count = 0 or #b.SomePKColumn is not null) AND (@C_Count = 0 or #c.SomePKColumn is not null) you'd have to test, though.- Jeff |
|
|
|
|
|
|
Using Multiple Select Criteria