CODEWITHSUNDEEP
sql
rlarsson
rlarsson

Reputation: 1

SQL return data based on checkbox values

I have a table that contains following columns: 

[Id], [Name], [Shampoo], [ShowerGel], [HairConditioner]

The [Shampoo], [ShowerGel] and [HairConditioner] columns specify the type of the entry.

I also have three checkboxes (bit variables) that tell whether I want to display specific entry or all of them.

For example, if only the ShowerGel checkbox is checked, then return values that belong to the type of ShowerGel.

Or if ShowerGel and HairConditioner checkboxes are checked, then return only the ones that belong to these types.

If all of the checkboxes are checked (or all of them are unchecked), then display all the entries.

The question is: how can I achieve it using ONLY the WHERE clause? If it's even possible.

Any kind of help/tips will be greatly appreciated.

Upvotes: 0

Views: 2338

Answers (2)

Vinit
Vinit

Reputation: 2607

Assuming the datatype of columns are bit, then you can simply have OR conditions in where clause, like below

Declare @IsShampoo bit, @IsShowerGel bit, @IsHairConditioner bit -- Checkbox values
select * 
from @YourTbale
where Shampoo = @IsShampoo
OR ShowerGel = @IsShowerGel
OR HairConditioner = @IsHairConditioner

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

Assuming @Shampoo, @ShowerGel and @HairConditioner are variables of type bit filled by the checkboxes' values. Further assuming you want to show an item if it matches any type. E.g. if something is a shampoo and a shower gel and the check box for shower gel is checked, but the one for shampoo isn't, show that something anyway.

WHERE @Shampoo = 0
      AND @ShowerGel = 0
      AND @HairConditioner = 0
       OR @Shampoo = 1
          AND [Shampoo] = 1
        OR @ShowerGel = 1
           AND [ShowerGel] = 1
         OR @HairConditioner = 1
            AND [HairConditioner] = 1

The first three handle the case if all are unchecked. The others the cases where some or all are checked.

Upvotes: 1

Related Questions