creating SQL command to return match or else everything else

Question

hi,

i have three checkboxs in my application. If the user ticks a combination of the boxes i want to return matches for the boxes ticked and in the case where a box is not checked i just want to return everything . Can i do this with single SQL command?

Answer 1

I recommend doing the following in the WHERE clause;

...
   AND (@OnlyNotApproved = 0 OR ApprovedDate IS NULL)

It is not one SQL command, but works very well for me. Basically the first part checks if the switch is set (checkbox selected). The second is the filter given the checkbox is selected. Here you can do whatever you would normally do.

Answer 2

sure. example below assumes SQL Server but you get the gist.

You could do it pretty easily using some Dynamic SQL Lets say you were passing your checkboxes to a sproc as bit values.

DECLARE bit @cb1
DECLARE bit @cb2
DECLARE bit @cb3

DECLARE nvarchar(max) @whereClause

IF(@cb1 = 1)
    SET @whereClause = @whereClause + ' AND col1 = ' + @cb1
IF(@cb2 = 1)
    SET @whereClause = @whereClause + ' AND col2 = ' + @cb2
IF(@cb3 = 1)
    SET @whereClause = @whereClause + ' AND col3 = ' + @cb3


DECLARE nvarchar(max) @sql 

SET @sql = 'SELECT * FROM Table WHERE 1 = 1' + @whereClause

exec (@sql)

Answer 3

Sure you can. If you compose your SQL SELECT statement in the code, then you just have to generate:

1. in case nothing or all is selected (check it using your language), you just issue non-filter version:

   SELECT ... FROM ...

2. in case some checkboxes are checked, you create add a WHERE clause to it:

   SELECT ... FROM ... WHERE MyTypeID IN (3, 5, 7)

This is single SQL command, but it is different depending on the selection, of course. Now, if you would like to use one stored procedure to do the job, then the implementation would depend on the database engine since what you need is to be able to pass multiple parameters. I would discourage using a procedure with just plain 3 parameters, because when you add another check-box, you will have to change the SQL procedure as well.

Answer 4

You can build a SQL statement with a dynamic where clause:

string query = "SELECT * FROM TheTable WHERE 1=1 ";
if (checkBlackOnly.Checked)
    query += "AND Color = 'Black' ";
if (checkWhiteOnly.Checked)
    query += "AND Color = 'White' ";

Or you can create a stored procedure with variables to do this:

CREATE PROCEDURE dbo.GetList
    @CheckBlackOnly bit
,   @CheckWhiteOnly bit
AS
SELECT *
FROM TheTable
WHERE
    (@CheckBlackOnly = 0 or (@CheckBlackOnly = 1 AND Color = 'Black'))
    AND (@CheckWhiteOnly = 0 or (@CheckWhiteOnly = 1 AND Color = 'White'))
    ....

Answer 5

The question did not specify a DB product or programming language. However it can be done with ANSI SQL in a cross-product manner.

Assuming a programming language that uses $var$ for variable insertion on strings.

On the server you get all selected values in a list, so if the first two boxes are selected you would have a GET/POST variable like

http://url?colors=black,white

so you build a query like this (pseudocode)

colors = POST['colors'];
colors_list = replace(colors, ',', "','"); // separate colors with single-quotes
sql = "WHERE ('$colors$' == '') OR (color IN ('$colors_list$'));";

and your DB will see:

WHERE ('black,white' == '') OR (color IN ('black','white')); -- some selections
WHERE ('' == '') OR (color IN ('')); -- nothing selected (matches all rows)

Which is a valid SQL query. The first condition matches any row when nothing is selected, otherwise the right side of the OR statement will match any row that is one of the colors. This query scales to an unlimited number of options without modification. The brackets around each clause are optional as well but I use them for clarity.

Naturally you will need to protect the string from SQL injection using parameters or escaping as you see fit. Otherwise a malicious value for colors will allow your DB to be attacked.

Answer 6

SELECT  *
FROM    table
WHERE   value IN
        (
        SELECT  option
        FROM    checked_options
        UNION ALL
        SELECT  option
        FROM    all_options
        WHERE   NOT EXISTS (
                SELECT  1
                FROM    checked_options
                )
        )

The inner subquery will return either the list of the checked options, or all possible options if the list is empty.

For MySQL, it will be better to use this:

    SELECT  *
    FROM    t_data
    WHERE   EXISTS (
            SELECT  1
            FROM    t_checked
            WHERE   session = 2
            )
            AND opt IN
            (
            SELECT  opt
            FROM    t_checked
            WHERE   session = 2
            )
    UNION ALL
    SELECT  *
    FROM    t_data
    WHERE   NOT EXISTS (
            SELECT  1
            FROM    t_checked
            WHERE   session = 2
            )

MySQL will notice IMPOSSIBLE WHERE on either of the SELECT's, and will execute only the appropriate one.

See this entry in my blog for performance detail:

• Selecting options

Answer 7

If you pass a null into the appropriate values, then it will compare that specific column against itself. If you pass a value, it will compare the column against the value

CREATE PROCEDURE MyCommand 
(
   @Check1 BIT = NULL,
   @Check2 BIT = NULL,
   @Check3 BIT = NULL
)

AS


SELECT     * 
FROM       Table
WHERE      Column1 = ISNULL(@Check1, Column1)
AND        Column2 = ISNULL(@Check2, Column2)
AND        Column3 = ISNULL(@Check3, Column3)