SQL question about Checkbox(Y/N) values

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL question about Checkbox(Y/N) values

Author

Topic

potn1
Starting Member

33 Posts

Posted - 2011-06-20 : 12:53:28

Hi I have a pretty basic query that displays: Person Name, Person_ID, Apt_Date from a ‘Person’ table. Within this table there are also about 15 other options. All of these other options are either a ‘Y’ or ‘N’ in the displayed query.

On my initial selection page I have up to 15 different checkboxes. These checkboxes are all incorporating those 15 (Yes or No) options in the table. The user can select from 1 to to ALL of these checkboxes.

My question: Is there a way to easier incorporate all of these possible selections in my SQL query besides using a CASE or a If/Else based on these 15 different options by just using multiple OR statements within my query to distinguish between them? I also thought about using a stored procedure depending on the length and possibly passing the values as parameters.

Any help is appreciated. Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-20 : 13:14:56

If you want to query against all the possible combinations of Y/N for 15 checkboxes (2 ^ 15), I'd use a bit-wise function.

Jim

Everyday I learn something that somebody else already knew

potn1
Starting Member

33 Posts

Posted - 2011-06-20 : 13:32:12

A bit-wise function would be used to query against all combinations even if the user only selects a few? For example if a user only selects 3/15 of these checkboxes, only 3 of these OR expressions will need to be populated for data to be displayed. So I was thinking of using a If/Else if any of those options were Checked then all matching columns in the DB with a value of ‘Y’ would be displayed. Is using a bit-wise function still best for what I would be looking for?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-20 : 16:42:01

Are you trying to just return 15 different "bits" or are you trying to filter (WHERE clause) the query based on 0-15 different "bits?"

This link might help you to provide more information so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

potn1
Starting Member

33 Posts

Posted - 2011-06-20 : 17:39:11

quote:
Originally posted by Lamprey

Are you trying to just return 15 different "bits" or are you trying to filter (WHERE clause) the query based on 0-15 different "bits?"

This link might help you to provide more information so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Filter (WHERE clause) the query based on any variation of the 0-15 different "bits."

Below is an example query, only using 2 of the 15 checkboxes. I was wondering if the best possible way to do this would just be to do 15 of these if/else statements.

SELECT DISTINCT person_name, person_id, apt_date
FROM person
WHERE apt_date IS NOT NULL
If GraduatedHS.Checked Then
OR (graduated_hs = 'Y')
ElseIf GraduatedCollege.Checked Then
OR (graduated_college = 'Y')
End If

potn1
Starting Member

33 Posts

Posted - 2011-06-23 : 10:37:33

I’ve got a side question based on this. I have my initial query below:

SELECT DISTINCT person_name, person_id, apt_date
FROM person
WHERE apt_date IS NOT NULL

Say this query returns 3 users when none of the checkboxes are checked which is what I am looking for. When I then start writing conditions for the checkboxes using OR from my WHERE clause those 3 users are being displayed and since I’m using OR the checkboxes are basically useless because all people show up in the original query and the OR are just continuing off of that.

My question: Is there a way to only focus on the checkbox conditions if ANY of them are checked. So if a user selects a checkbox the 3 original users populated from the query will not show up unless they meet the checkbox conditions.

Thanks in advance!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 10:46:25

I'll try one more time:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

potn1
Starting Member

33 Posts

Posted - 2011-06-23 : 11:03:39

Question
How do I get a query to first populate and then to only show specific users based off of a checkbox selection using OR conditions in my WHERE clause?

Sample
I have 3 users displayed from this query:

SELECT DISTINCT person_name, person_id, apt_date
FROM person
WHERE apt_date IS NOT NULL

Attempted
*the structure of the language is different in my environment, but this should give you an idea of what I was trying to do.
SELECT DISTINCT person_name, person_id, apt_date
FROM person
WHERE apt_date IS NOT NULL
If GraduatedHS.Checked Then
OR (graduated_hs = 'Y')
ElseIf GraduatedCollege.Checked Then
OR (graduated_college = 'Y')
End If

Desired Result
When I begin to use my checkbox OR conditions those users should not show up unless ANY of the checkbox conditions are met.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 11:28:49

Still no DDL or DML, but here is a shot in the dark:

CREATE PROCEDURE SomeThing(@GraduatedHS CHAR(1), @GraduatedCollege CHAR(1))
AS
BEGIN

    SELECT DISTINCT person_name, person_id, apt_date
    FROM person
    WHERE 
        person.apt_date IS NOT NULL
        AND
        (
            Person.graduated_hs = GraduatedHS
            OR Person.graduated_college = GraduatedCollege
        )
END

potn1
Starting Member

33 Posts

Posted - 2011-06-23 : 11:53:51

quote:

Originally posted by Lamprey

Still no DDL or DML, but here is a shot in the dark:

CREATE PROCEDURE SomeThing(@GraduatedHS CHAR(1), @GraduatedCollege CHAR(1))
AS
BEGIN

    SELECT DISTINCT person_name, person_id, apt_date
    FROM person
    WHERE 
        person.apt_date IS NOT NULL
        AND
        (
            Person.graduated_hs = GraduatedHS
            OR Person.graduated_college = GraduatedCollege
        )
END

Thanks for the response. I have tried something very similar to that but at times the checkboxes may not be checked and then that 'AND' is just there with no conditions to filter through. So that AND condition from the WHERE clause is needed only when a checkbox selection is checked. I also cannot place the AND before the first checkbox condition because I never know what conditions the user will select.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-06-23 : 12:36:36

Humm, maybe you are looking for a catch-all query?

If so, check out this link:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources