Sql Server Programming :: Help comparing field values


	Help comparing field values I am trying to see if field1 is in field2, field3, field4, or field5 by using a simple field1 IN(field2, field3, field4, field5) statement. However, this seems to work sporadically, and I am not sure how to accomplish this without using a loop. Does anyone have a suggestion to keep this data comparison simple? (I have already tried trimming the field values as well to no avail). Thank you in advance! That will work in principal, the IN is simply saying... field1 = field2 OR field1 = field3 OR field1 = field4 OR field1 = field5 The complexity may be coming from data types - varchar / char etc...; do you have the SQL you are using? Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] "Sandy" <S@discussions.microsoft.com> wrote in message news:70DF45BD-2311-4A62-B61F-F856F3BBCD8F@microsoft.com... - Hide quoted text - >I am trying to see if field1 is in field2, field3, field4, or field5 by >using > a simple field1 IN(field2, field3, field4, field5) statement. However, > this > seems to work sporadically, and I am not sure how to accomplish this > without > using a loop. Does anyone have a suggestion to keep this data comparison > simple? (I have already tried trimming the field values as well to no > avail). > Thank you in advance! Hi! Yes, sure do - here ya go: UPDATE tvg_compare_progrec SET chk_tf_cast_first_name1 = 1 FROM tvg_compare_progrec A INNER JOIN fyi_conversion.dbo.program_mapping B ON A.tf_database_key_3 = cast(B.tvg_id AS varchar (12)) INNER JOIN fyi_compare_progrec C ON cast(B.fyi_id AS varchar (12)) = C.tf_database_key_3 WHERE (A.tf_cast_first_name1 IS NOT NULL) AND (A.tf_cast_first_name1 NOT IN (C.tf_cast_first_name, C.tf_cast_first_name1, C.tf_cast_first_name2, C.tf_cast_first_name3, C.tf_cast_first_name4)) It is so strange how it works for most but randomly will not for one of the field comparisons.. Thanks for your assistance! Sandy - Hide quoted text - "Tony Rogerson" wrote: > That will work in principal, the IN is simply saying... > field1 = field2 OR field1 = field3 OR field1 = field4 OR field1 = field5 > The complexity may be coming from data types - varchar / char etc...; do you > have the SQL you are using? > Tony. > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > "Sandy" <S@discussions.microsoft.com> wrote in message > news:70DF45BD-2311-4A62-B61F-F856F3BBCD8F@microsoft.com... > >I am trying to see if field1 is in field2, field3, field4, or field5 by > >using > > a simple field1 IN(field2, field3, field4, field5) statement. However, > > this > > seems to work sporadically, and I am not sure how to accomplish this > > without > > using a loop. Does anyone have a suggestion to keep this data comparison > > simple? (I have already tried trimming the field values as well to no > > avail). > > Thank you in advance! Thanks Tony - I figured out my problem. Cheers! - Hide quoted text - "Sandy" wrote: > Hi! Yes, sure do - here ya go: > UPDATE tvg_compare_progrec > SET chk_tf_cast_first_name1 = 1 > FROM tvg_compare_progrec A > INNER JOIN fyi_conversion.dbo.program_mapping B ON > A.tf_database_key_3 = cast(B.tvg_id AS varchar (12)) > INNER JOIN fyi_compare_progrec C ON > cast(B.fyi_id AS varchar (12)) = C.tf_database_key_3 > WHERE > (A.tf_cast_first_name1 IS NOT NULL) > AND (A.tf_cast_first_name1 NOT IN (C.tf_cast_first_name, > C.tf_cast_first_name1, > C.tf_cast_first_name2, C.tf_cast_first_name3, C.tf_cast_first_name4)) > It is so strange how it works for most but randomly will not for one of the > field comparisons.. > Thanks for your assistance! > Sandy > "Tony Rogerson" wrote: > > That will work in principal, the IN is simply saying... > > field1 = field2 OR field1 = field3 OR field1 = field4 OR field1 = field5 > > The complexity may be coming from data types - varchar / char etc...; do you > > have the SQL you are using? > > Tony. > > -- > > Tony Rogerson, SQL Server MVP > > http://sqlblogcasts.com/blogs/tonyrogerson > > [Ramblings from the field from a SQL consultant] > > http://sqlserverfaq.com > > [UK SQL User Community] > > "Sandy" <S@discussions.microsoft.com> wrote in message > > news:70DF45BD-2311-4A62-B61F-F856F3BBCD8F@microsoft.com... > > >I am trying to see if field1 is in field2, field3, field4, or field5 by > > >using > > > a simple field1 IN(field2, field3, field4, field5) statement. However, > > > this > > > seems to work sporadically, and I am not sure how to accomplish this > > > without > > > using a loop. Does anyone have a suggestion to keep this data comparison > > > simple? (I have already tried trimming the field values as well to no > > > avail). > > > Thank you in advance!