locked
Filter with multiple choices RRS feed

  • Question

  • I am writing a C# program. In this program, user will have a datagridview and a combobox where he can selects the choices in the combobox to display what he wants to see in the datagridview. The datagridview is bound to this table (this is just a sample);

    ID   Item                             Price

    1    World History               $15.50
    2    Learning Tennis            $9.99
    3    1000 Recipes               $18.00
    4    Harry Potter                 $25.00
    5    C# Programing             $15.50

    I want to write a SQL querry which allows the user to have multiple choices as follow:

    Choice one: return all items
    Choice two: return item(s) where price = $15.50
    Choice three: return item(s) where price > $20.00
    Choice four: return item(s) where price < $20.00

    I know I can writh multiple querries for the choices, but is it possible to put it all in one querry?
    Friday, September 26, 2008 5:35 PM

Answers

  • Something like this?

     

    Code Snippet

    DECLARE @t TABLE (ID int, Item VARCHAR(30), Price MONEY)

    INSERT INTO @t (ID, Item, Price) SELECT 1,'World History',$15.50

    INSERT INTO @t (ID, Item, Price) SELECT 2,'Learning Tennis',$9.99

    INSERT INTO @t (ID, Item, Price) SELECT 3,'1000 Recipes',$18.00

    INSERT INTO @t (ID, Item, Price) SELECT 4,'Harry Potter',$25.00

    INSERT INTO @t (ID, Item, Price) SELECT 5,'C# Programing',$15.50

     

    DECLARE @value MONEY

    DECLARE @condition CHAR(1)

     

    SELECT @value = 15.50

    SELECT @condition = '='

     

    SELECT

    *

    FROM @t

    WHERE

    CASE

    WHEN @condition = '=' AND Price = @value THEN 1

    WHEN @condition = '<' AND Price < @value THEN 1

    WHEN @condition = '>' AND Price > @value THEN 1

    WHEN @condition = '' THEN 1

    END = 1

    /*

    ID          Item                           Price

    ----------- ------------------------------ ---------------------

    1           World History                  15.50

    5           C# Programing                  15.50

    */

     

     

    Friday, September 26, 2008 6:21 PM

All replies

  • Something like this?

     

    Code Snippet

    DECLARE @t TABLE (ID int, Item VARCHAR(30), Price MONEY)

    INSERT INTO @t (ID, Item, Price) SELECT 1,'World History',$15.50

    INSERT INTO @t (ID, Item, Price) SELECT 2,'Learning Tennis',$9.99

    INSERT INTO @t (ID, Item, Price) SELECT 3,'1000 Recipes',$18.00

    INSERT INTO @t (ID, Item, Price) SELECT 4,'Harry Potter',$25.00

    INSERT INTO @t (ID, Item, Price) SELECT 5,'C# Programing',$15.50

     

    DECLARE @value MONEY

    DECLARE @condition CHAR(1)

     

    SELECT @value = 15.50

    SELECT @condition = '='

     

    SELECT

    *

    FROM @t

    WHERE

    CASE

    WHEN @condition = '=' AND Price = @value THEN 1

    WHEN @condition = '<' AND Price < @value THEN 1

    WHEN @condition = '>' AND Price > @value THEN 1

    WHEN @condition = '' THEN 1

    END = 1

    /*

    ID          Item                           Price

    ----------- ------------------------------ ---------------------

    1           World History                  15.50

    5           C# Programing                  15.50

    */

     

     

    Friday, September 26, 2008 6:21 PM
  • Can you explain what "THEN 1" mean? Thanks.
    Saturday, September 27, 2008 1:58 AM
  • If the condition matches, the CASE expression returns 1. And the WHERE clause returns only those rows where the CASE expression returns 1.

    Saturday, September 27, 2008 4:57 AM