To Retrieve the records by Negating the First condition

• ravi kiran-441413

  SSC Rookie

  Points: 30

  More actions

  September 12, 2008 at 9:11 am

  #75314

  Hi,

  I have a situation where :

  Update table_A

  Set C_flag = 'W'

  WHERE C_PERSONNUM = '01'

  AND X_CODE IS NOT NULL

  AND X_CODE IN ( Select X_CODE from table_A Where X_CODE NOT IN ( select Y_CODE from table_B where

  Z_CODE = 'ABC' ) )

  Here is the problem, the next condition i want to have is:

  if X_CODE is in ( select Y_CODE from table_B where Z_CODE = 'ABC' )

  and (WORKLOCATION NOT IN (select country_code from table_C ) OR

  NATIONALITY NOT IN (select country_code from table_C )

  Basically i am checking in the first condition if it is not in table_B (X_CODE in table_A is same as Y_CODE in table_B)

  and in the second condition i want to see if it is in that table_B (negating the above condition and have additional conditions i.e. worklocation or Nationality ...)

  How can i achieve this..??

  I hope i am clear.

  Any suggestion would be appreciated.

  Thanks,

  R

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  September 12, 2008 at 12:53 pm

  #871275

  If I'm reading this correctly, you'd want to do something like this:

  UPDATE table_A

  SET C_Flag = 'W'

  FROM table_A A

  INNER JOIN table_B B ON A.X_CODE = B.Y_CODE

  LEFT JOIN table_C C ON A.WORKLOCATION = C.Country_Code

  LEFT JOIN table_C C2 ON A.NATIONALITY = C2.Country_Code

  WHERE A.X_CODE IS NOT NULL

  AND ISNULL(B.Z_CODE,'') <> 'ABC'

  AND A.C_PERSONNUM = '01'

  AND (C.Country_Code IS NULL

  OR C2.Country_Code IS NULL)

  That said, I'm not positive I'm understanding your true intent, so I'd try a select with that code before doing anything else.

  Select:

  SELECT A.*

  FROM table_A A

  INNER JOIN table_B B ON A.X_CODE = B.Y_CODE

  LEFT JOIN table_C C ON A.WORKLOCATION = C.Country_Code

  LEFT JOIN table_C C2 ON A.NATIONALITY = C2.Country_Code

  WHERE A.X_CODE IS NOT NULL

  AND ISNULL(B.Z_CODE,'') <> 'ABC'

  AND A.C_PERSONNUM = '01'

  AND (C.Country_Code IS NULL

  OR C2.Country_Code IS NULL)

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ravi kiran-441413

  SSC Rookie

  Points: 30

  More actions

  September 12, 2008 at 2:53 pm

  #871344

  hi,

  thanks , You were right with the Query u suggested.

  The reason i wasnt getting when i tried the above method (infact several other ways) was due to inconsistent data.

  Thanks for your suggestion.

  appreciate that.

  thanks,

  R