Using IN operator to find id exist or not

Question

Post reply

Using IN operator to find id exist or not

selpoivre

Say Hey Kid

Points: 702
More actions
October 26, 2014 at 7:48 pm

#291433

CREATE TABLE [REV_BINDERS2]( [OFFICE_ID] [int] NOT NULL) insert into REV_BINDERS2(OFFICE_ID) select '1126' union all select '1127' union all select '1128' union all select '1129' union all select '1130' union all select '1131' union all select '1132' union all select '1133' union all select '1134'

I have to put these office_id ('1126','1127','1128','9999') in IN operator in a query and result should come

Office_ID Id_Exist

1126 Yes

1127 Yes

1128 Yes

9999 No

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

selpoivre (10/26/2014)

CREATE TABLE [REV_BINDERS2]( [OFFICE_ID] [int] NOT NULL) insert into REV_BINDERS2(OFFICE_ID) select '1126' union all select '1127' union all select '1128' union all select '1129' union all select '1130' union all select '1131' union all select '1132' union all select '1133' union all select '1134'

I have to put these office_id ('1126','1127','1128','9999') in IN operator in a query and result should come

Office_ID Id_Exist

1126 Yes

1127 Yes

1128 Yes

9999 No

What are you trying to accomplish? Your question is incomplete.

selpoivre Say Hey Kid Points: 702 More actions · Answer 2

selpoivre

Say Hey Kid

Points: 702

October 26, 2014 at 10:15 pm

#1754830

I want such query in which i put specific IDs in IN operator and give me result which are present in table and which are not.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

CASE WHEN office_id ('1126','1127','1128','9999') THEN 'Yes' ELSE 'No END as ID_Exists

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Daniel Matthee Mr or Mrs. 500 Points: 569 More actions · Answer 4

HI

maybe this will also help. Thou not an IN operator but effective

Kind Regards



CREATE TABLE #REV_BINDERS2

    (

      [OFFICE_ID] [INT] NOT NULL

    )

INSERT  INTO #REV_BINDERS2

        ( OFFICE_ID

        )

        SELECT  '1126'        UNION ALL

        SELECT  '1127'        UNION ALL

        SELECT  '1128'        UNION ALL

        SELECT  '1129'        UNION ALL

        SELECT  '1130'        UNION ALL

        SELECT  '1131'        UNION ALL

        SELECT  '1132'        UNION ALL

        SELECT  '1133'        UNION ALL

        SELECT  '1134'

CREATE TABLE #Ids_To_Find

    (

      [OFFICE_ID] [INT] NOT NULL

    )

INSERT  INTO #Ids_To_Find

        ( OFFICE_ID

        )

        SELECT  '1126'        UNION ALL

        SELECT  '1127'        UNION ALL

        SELECT  '1128'        UNION ALL

        SELECT  '1132'        UNION ALL

        SELECT  '2222'

SELECT  a.OFFICE_ID ,

        CASE WHEN b.OFFICE_ID IS NULL THEN 'No'

             ELSE 'Yes'

        END AS Id_Exist

FROM    #REV_BINDERS2 a

LEFT JOIN #Ids_To_Find b ON b.OFFICE_ID = a.OFFICE_ID

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 5

selpoivre (10/26/2014)

I want such query in which i put specific IDs in IN operator and give me result which are present in table and which are not.

Will you run this from SSMS every time you need it, or will it be hooked up to some kind of user interface? Will the list always be a comma separated list?

Don Simpson

I'm not sure about Heisenberg.

selpoivre Say Hey Kid Points: 702 More actions · Answer 6

Hi Guys

Using this query select OFFICE_ID,



  CASE WHEN OFFICE_ID in('1126','1127','1128','9999') THEN 'Yes' ELSE 'No ID_Exists' end

from [REV_BINDERS2]

i m getting this

OFFICE_ID (No column name)

1126 Yes

1127 Yes

1128 Yes

1129 No ID_Exists

1130 No ID_Exists

1131 No ID_Exists

1132 No ID_Exists

1133 No ID_Exists

1134 No ID_Exists

Values i put in IN operator only those values should give result like this

Office_ID Id_Exist

1126 Yes

1127 Yes

1128 Yes

9999 No ID_Exists

Daniel Matthee Mr or Mrs. 500 Points: 569 More actions · Answer 7

Hi

Try this way

as my prev piece of code but only change the left join between the tables.



CREATE TABLE #REV_BINDERS2

    (

      [OFFICE_ID] [INT] NOT NULL

    )

INSERT  INTO #REV_BINDERS2

        ( OFFICE_ID

        )

        SELECT  '1126'        UNION ALL

        SELECT  '1127'        UNION ALL

        SELECT  '1128'        UNION ALL

        SELECT  '1129'        UNION ALL

        SELECT  '1130'        UNION ALL

        SELECT  '1131'        UNION ALL

        SELECT  '1132'        UNION ALL

        SELECT  '1133'        UNION ALL

        SELECT  '1134'

CREATE TABLE #Ids_To_Find

    (

      [OFFICE_ID] [INT] NOT NULL

    )

INSERT  INTO #Ids_To_Find

        ( OFFICE_ID

        )

        SELECT  '1126'        UNION ALL

        SELECT  '1127'        UNION ALL

        SELECT  '1128'        UNION ALL

        SELECT  '1132'        UNION ALL

        SELECT  '2222'

SELECT  a.OFFICE_ID ,

        CASE WHEN b.OFFICE_ID IS NULL THEN 'No'

             ELSE 'Yes'

        END AS Id_Exist

FROM	#Ids_To_Find   a

        LEFT JOIN  #REV_BINDERS2 b ON b.OFFICE_ID = a.OFFICE_ID

--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 8

Daniel Matthee (10/28/2014)

Hi

Try this way

as my prev piece of code but only change the left join between the tables.

CREATE TABLE #REV_BINDERS2 ( [OFFICE_ID] [INT] NOT NULL ) INSERT INTO #REV_BINDERS2 ( OFFICE_ID ) SELECT '1126' UNION ALL SELECT '1127' UNION ALL SELECT '1128' UNION ALL SELECT '1129' UNION ALL SELECT '1130' UNION ALL SELECT '1131' UNION ALL SELECT '1132' UNION ALL SELECT '1133' UNION ALL SELECT '1134' CREATE TABLE #Ids_To_Find ( [OFFICE_ID] [INT] NOT NULL ) INSERT INTO #Ids_To_Find ( OFFICE_ID ) SELECT '1126' UNION ALL SELECT '1127' UNION ALL SELECT '1128' UNION ALL SELECT '1132' UNION ALL SELECT '2222' SELECT a.OFFICE_ID , CASE WHEN b.OFFICE_ID IS NULL THEN 'No' ELSE 'Yes' END AS Id_Exist FROM #Ids_To_Find a LEFT JOIN #REV_BINDERS2 b ON b.OFFICE_ID = a.OFFICE_ID

Without using the temp table:



with testvalues as (

select office_id from (values (1126),(1127),(1128),(9999))dt(office_id)

)

select

    tv.office_id,

    CASE WHEN OFFICE_ID in (select office_id from testvalues) THEN 'Yes' ELSE 'No' END as ID_Exists

from

    testvalues tv

    left outer join REV_BINDERS2 rb

        on rb.OFFICE_ID = tv.office_id