CODEWITHSUNDEEP
mysqlstored-procedures
Cindy Burker
Cindy Burker

Reputation: 127

SQL returning true or false based on whether an elament exist in a table

Assume I have a table

    CREATE TABLE list_of_values (
        components VARCHAR(20)
        );

INSERT INTO list_of_values VALUES ("a")
INSERT INTO list_of_values VALUES ("b")
INSERT INTO list_of_values VALUES ("b")
INSERT INTO list_of_values VALUES ("c")
INSERT INTO list_of_values VALUES ("c")
INSERT INTO list_of_values VALUES ("d")
INSERT INTO list_of_values VALUES ("e")
INSERT INTO list_of_values VALUES ("f")
INSERT INTO list_of_values VALUES ("g")

and

SET @first = "a"
SET @second = "b"
SET @third = "c"

Let's assume the values of "list_of_values" looks like the below;

list_of_values = ["a","b","b","c","c","d","e","f","g"]

I am trying to write an if loop where it would do action "FIRST_ACTION" if none of the @first, @second, or @third values exist in the table, and do "SECOND_ACTION" if elsewise.

I am currently doing

IF (SELECT distinct(1) FROM list_of_values as l WHERE l.components = @first OR l.components = @second OR l.components = @third) IS NULL THEN
    SET @decision_matirx = "DO FIRST_ACTION"
ELSE 
    SET @decision_matirx = "DO SECOND_ACTION"
END IF;

However, I can not get it to work. Would you please be able to help me?

Upvotes: 0

Views: 21

Answers (1)

Barmar
Barmar

Reputation: 781235

Use EXISTS

IF EXISTS(
    SELECT 1
    FROM list_of_values
    WHERE components IN (@first, @second, @third)
) 
THEN SET @decision_matrix = 'DO SECOND ACTION';
ELSE SET @decision_matrix = 'DO FIRST ACTION';
END IF;

DEMO

Upvotes: 2

Related Questions