CODEWITHSUNDEEP
mysql
CodeIgniter
CodeIgniter

Reputation: 85

How to use list in MYSQL IN Clause

select field_name,field_unit,field_range 
from observation_template 
where template_name='abc' 
  and field_name not in ('[wd, ef]')

I am having a list which i get from another sql query,eg: [wd,ef] ,i need to get the new list of data which is not in the list while i execute the sql query,list with sqare braces ,how can i eliminate braces and write query properly

Upvotes: 1

Views: 267

Answers (4)

Aswathy
Aswathy

Reputation: 189

You can simply write a sub query in NOT IN clause, like :

select field_name,field_unit,field_range from observation_template where template_name='abc' and field_name NOT IN (sub query for the list)

Upvotes: 2

Ian
Ian

Reputation: 1179

Try stored procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_get_observation_template $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_observation_template`(
    IN `p_field_name` VARCHAR(255)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '查詢客服人員資訊'
BEGIN
    SET @sql = CONCAT("
    SELECT
        `field_name`,
        `field_unit`,
        `field_range`
    FROM observation_template
    WHERE `template_name` = 'abc'
    AND `field_name` NOT IN ('", p_field_name, "')");
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

How to call

CALL sp_get_observation_template("wd, ef");

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13026

You can use find_in_set() with replace() function available in mysql.

select field_name,field_unit,field_range from observation_template 
where template_name='abc' and 
            find_in_set(field_name
               , replace(replace(replace('[wd, ef]', '[', ''), ']', ''), ' ', '')
            ) = 0

try this dbfiddle.

Upvotes: 0

BInjal Patel
BInjal Patel

Reputation: 331

select field_name,field_unit,field_range from observation_template where template_name='abc' and field_name NOT IN("wd","ef");

Upvotes: 0

Related Questions