How to use true, false and both condition in sql server

Question

3.00/5 (2 votes)

See more:

Hi,
I have a check box in my design (Active). It stores either 0 or 1. I have to search items on the basis of check box value, ie., either active or inactive or both. For active I can use 1 and for inactive I can use 0. But I need to use all the condition (1,0,1&0).

Posted 31-Jul-14 23:59pm

My Doubt

Updated 1-Aug-14 1:21am

v5

Add a Solution

Comments

Namith Krishnan E 1-Aug-14 6:24am

specify more details about your requirement

3 solutions

Solution 1

That's a little odd: a number can't be both 0 and 1 at the same time, so searching for "is zero" and "is one" will always fail, because if one test passes, the other automatically fails, so the result is always "no match".
And since the value can only be 0 or 1, searching for "is zero" or "is one" will also be true since the value will always be one of those values.

It's like tossing a coin: If you choice "Both heads and tails" you will lose - because the coin will be one or the other, and if you chose "either heads or tails" you will win because it will always show one or the other. (You might get punched if you try this in the real world though ;) ).

I think you need to rethink exactly what you are trying to do - I'm not sure you quite undersatnd, or you have explained it very badly! :laugh:

Posted 1-Aug-14 0:07am

OriginalGriff

Updated 1-Aug-14 1:29am

v2

Comments

My Doubt 1-Aug-14 7:04am

I have updated my question

OriginalGriff 1-Aug-14 7:22am

And it's still not a lot better: Remember that we can't see your screen, access your HDD, or read your mind.

My Doubt 1-Aug-14 7:28am

Ok I can explain my situation.

My Doubt 1-Aug-14 7:41am

I need to perform a search action in my project. I have a database which stores all the data. I have a check box named Active which give values either true (1) or false(0). I have lots of columns in my table like ItemID, ItemDescri, Active(my check box) etc. When ever I add an Item to my table I may tick or untick my check box. ie, either 0 or 1 will be stored in db. Here is my table:
ItemID ItemDescri Active
1 abc 1
2 bcd 1
3 ggd 0

I have used a check box in the search criteria. It selects either true or false. But the problem is that an user search an item with ItemID 3 and check box is ticked(1). So he can't able to see any result because for ItemID 3 active is 0. So the user may misunderstood that there is no item with itemID 3 and he will try to insert new item with ID 3. So there will be a conflict occurs. So that I have created a drop down list instead of check box in search. Values are: All( Both 1 and 0), Active(1) and Inactive(0).

Solution 3

Quote:
I have to search items on the basis of check box value, ie., either active or inactive or both.

If you just follow your "pseudo" code above you are really saying

SQL

WHERE checked = 1 OR checked = 0

which really means you don't need the check at all.

Posted 1-Aug-14 1:33am

ZurdoDev

Comments

My Doubt 1-Aug-14 8:06am

No I have explained above about the misunderstanding of user. So I need to use check all condition too.

ZurdoDev 1-Aug-14 8:09am

Then use solution 2. The database can support tri-state but only if you allow null. The default net checkbox cannot support tri-state though (as I recall) so you may want to look for a different control.

My Doubt 2-Aug-14 5:01am

ok. That's correct

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Manfred Rudolf Bihy · Accepted Answer · 2014-08-01T01:29:00

Solution 2

What you are talking about sounds like tri-state problem.
The checkmark has been set == 1
The checkmark has been unset == 0
The checkmark neither been set nor unset == third state.

One way to store this third state in a DB would be allowing that column to be NULL.

You could check for this like so:

SQL

SELECT *
FROM MyTable mt
WEHRE mt.TriStateSomething = NULL

Cheers!

Posted 1-Aug-14 1:29am

Manfred Rudolf Bihy

Comments

My Doubt 1-Aug-14 8:09am

Hey ,
My requirement is opposite of what you have explained. You said that neither 1 nor 0 but I need 1 and 0 (both).

Kumarbs 1-Aug-14 8:40am

You can maintain null value for 1&0 condition.