How to check whether an element is present in the database table or not?

Question

I am new to this database and I am stuck on a problem. I have a query which returns more than one row as output. I want to check whether the query returns a value or not based on a condition. I am using MySQL and a stored procedure.

CREATE PROCEDURE getSearchFilter(IN searchname varchar(220),categoryid int, categoryheading varchar(220),catdiscription varchar(220))
DETERMINISTIC
BEGIN 
if SELECT IF( EXISTS(SELECT distinct categorie_heading FROM product_meta_data WHERE categorie_heading='material'), 1, 0) then

//code
else
//code
end if;

END

SELECT distinct categorie_heading FROM product_meta_data WHERE categorie_heading='material';

It returns multiple rows, but I want to check whether 'material' is present in the table or not.

possible duplicate of [Best way to test if a row exists in a MySQL table](http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table) — Laurent W., Sep 27 '13 at 13:10

Abhineet Prasad · Accepted Answer · 2013-09-27T14:21:04.700

1

The following query will result in one 1 if the value exists in the table:

SELECT DISTINCT 1 FROM product_meta WHERE categorie_heading="material";

if the column has atleast one row with 'material' in it, 1 will be returned else null.

edited Sep 27 '13 at 14:21

answered Sep 27 '13 at 13:12

Abhineet Prasad

1,271
2
11
14

the above query returns multiple rows and based on that i have to check in the "if" statement....I got the error saying....#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT IF( EXISTS(SELECT 1 FROM product_meta WHERE categorie_heading='material')' at line 4 – user2651906 Sep 27 '13 at 13:18
Question is not about selecting results, it is just querying if the value exists or not. – Kuzgun Sep 27 '13 at 14:14
@Kuzgun the query would return one "1" if atleast one row if the value exists. isnt that what the user wanted? – Abhineet Prasad Sep 27 '13 at 14:17
Sorry, I read it wrong but cannot undo the vote. It says the answer is locked, can you edit it? – Kuzgun Sep 27 '13 at 14:20
Please, see http://stackoverflow.com/questions/1676551/best-way-to-test-if-a-row-exists-in-a-mysql-table ... – Laurent W. Sep 27 '13 at 15:10

score 0 · Answer 2 · answered Sep 27 '13 at 14:12

You are checking the condition and later selecting everything without checking condition. If you want just if it exits or not use this:

CREATE PROCEDURE getSearchFilter(IN searchname varchar(220),categoryid int, categoryheading varchar(220),catdiscription varchar(220))
DETERMINISTIC
BEGIN 
if SELECT IF( EXISTS(SELECT distinct categorie_heading FROM product_meta_data WHERE categorie_heading='material'), 1, 0) then

SELECT 'True'
else
SELECT 'False'
end if;

END

If you want to see the results if it exists, use it this way:

CREATE PROCEDURE getSearchFilter(IN searchname varchar(220),categoryid int, categoryheading varchar(220),catdiscription varchar(220))
    DETERMINISTIC
    BEGIN 
    if SELECT IF( EXISTS(SELECT distinct categorie_heading FROM product_meta_data WHERE categorie_heading='material'), 1, 0) then

    SELECT distinct categorie_heading FROM product_meta_data WHERE categorie_heading='material';
    else
    //you can select a value here or not
    end if;

    END

How to check whether an element is present in the database table or not?

2 Answers2