0

I have a requirement where I need to check all the values of IN clause exists in one column of SQL Server table or not.

How will I be able to find this?

I was trying something like this.

IF NOT EXISTS (
        SELECT NULL
        FROM workflow_attributes
        WHERE attribute_name IN (
                'Mukund_Test_1'
                ,'Mukund_Test_2'
                ,'Mukund_Test_3'
                )
        )
BEGIN
    PRINT 'not exist'
END
ELSE
BEGIN
    PRINT 'exists'
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mukund
  • 1,679
  • 1
  • 11
  • 20

2 Answers2

3

One method is to use GROUP BY and COUNT() (or COUNT(DISTINCT)):

IF 3 = (SELECT COUNT(DISTINCT attribute_name)
        FROM workflow_attributes
        WHERE attribute_name IN ('Mukund_Test_1', 'Mukund_Test_2', 'Mukund_Test_3')
       )
BEGIN
    PRINT 'exists'
END
ELSE
BEGIN
    PRINT 'not exists'
END
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • can you please help me in this [question](https://stackoverflow.com/questions/44926109/need-where-in-which-work-as-and-for-each-value-with-join-query-in-codeigniter) – always-a-learner Jul 06 '17 at 04:19
0

Declare @tbl as table (attribute_name varchar(100))
insert into @tbl values ('Mukund_Test_1'),('Mukund_Test_2'),('Mukund_Test_3') 
IF EXISTS (
        SELECT 1
        FROM workflow_attributes w left join @tbl t on w.attribute_name = t.attribute_name
        WHERE t.attribute_name is null
        )
BEGIN
    PRINT 'not exist'
END
ELSE
BEGIN
    PRINT 'exists'
END
sunny
  • 11
  • 3
  • 1
    While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – Pradeepal Sudeshana Feb 11 '21 at 14:10