Return True if specific value exists in table - sql

Question

I want to create an SQL query that will return True if a specific value exists in a specific column; if not, then it will return False.

I know that I can create something like 'SELECT something FROM somewhere WHERE something'. In this case I don't want to select anything, just to check.

My question is how can I do it.

You can use SELECT COUNT(something) FROM somewhere WHERE something — EnriMR, Jun 13 '15 at 10:29

score 1 · Answer 1 · answered Jun 13 '15 at 10:31

1

You can use the IIf function:

SELECT IIf(something = 'Some value', True, False) FROM somewhere;

answered Jun 13 '15 at 10:31

Robby Cornelissen

91,784
22
134
156

score 1 · Answer 2 · answered Jun 13 '15 at 16:08

In Access, you can use a DCount expression to count the number of rows where your something field contains 'some value'. Here is an example copied from the Immediate window:

Debug.Print DCount("*", "somewhere", "something='some value'")
 1
Debug.Print DCount("*", "somewhere", "something='BOGUS'")
 0

You could adapt that to give you True when the count is greater than zero or False for count of zero:

Debug.Print (DCount("*", "somewhere", "something='some value'") > 0)
True
Debug.Print (DCount("*", "somewhere", "something='BOGUS'") > 0)
False

If you want to do it from a query, this one will return -1 for True and zero for False:

SELECT (DCount("*", "somewhere", "something='some value'") > 0) AS value_exists;

Or you could use a Format expression to display those values as strings: "True"; or "False":

SELECT Format(DCount("*", "somewhere", "something='some value'") > 0, 'True/False') AS value_exists;

score 1 · Answer 3 · answered Jun 13 '15 at 16:13

1

As the name implies, DLookup is for this:

SomevalueExists = Not IsNull(DLookup("Id", "somewhere", "somefield = somevalue"))

answered Jun 13 '15 at 16:13

Gustav

53,498
7
29
55

score 0 · Answer 4 · answered Jun 13 '15 at 10:42

0

try this:

select case when x is null then false else true end
from (select max(somecol) x
from sometable
where somecol = somevalue) a

answered Jun 13 '15 at 10:42

Bohemian

412,405
93
575
722

score 0 · Answer 5 · answered Jun 13 '15 at 15:36

just use

select count(*) from tableName where columnName = '$variableInput';

if you plan on reusing this you might as well make it a prepared statement that you can call upon through whichever interface you design to work with your database. If the returned value is greater than zero you know it to be true, for instance

    if(preparedStatement($variableInput)>0)
{
$flag = true;
}
else
{
$flag = false;
}

Return True if specific value exists in table - sql

5 Answers5