MSSQL Sql Not working or partially working

• carlos.antunez

  Valued Member

  Points: 69

  More actions

  April 13, 2013 at 8:16 pm

  #273028

  Hi All and thanks in advanced.

  I Have in my MSSQL Database a Table named TIME_OFF_CODES , in where I have the following Time Off Codes under the OFF_CODES Column "SICK" and "FAMILY MEMBER SICK"

  Now when I do my Query like this

  SELECT * FROM TIME_OFF_CODES WHERE (OFF_CODE = 'SICK') AND (OFF_CODE = 'FAMILY MEMBER SICK')

  I Get no Records, but when I do

  SELECT * FROM TIME_OFF_CODES WHERE (OFF_CODE = 'SICK')

  I get 42 records of "SICK"

  and when I do my query like This

  SELECT * FROM TIME_OFF_CODES WHERE (OFF_CODE = 'FAMILY MEMBER SICK')

  I get 67 records of "FAMILY MEMBER SICK"

  Any Ideas why?

  Thanks for help.

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  April 13, 2013 at 10:14 pm

  #1606284

  Change the "AND" to an "OR".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• carlos.antunez

  Valued Member

  Points: 69

  More actions

  April 15, 2013 at 9:07 am

  #1606498

  Thanks for the help

  I guess that I should put down the entire query.

  Your help was great and helped me out to accomplish, however, when the entire query was structured, your help didn't work, and it wasn't because of you.

  Let me show you the entire query and also show you why it didn't work, and also what I did so that it could work

  SELECT *

  FROM TIME_OFF_CODES

  WHERE LOCATION = 'SOUTH'

  AND AREA = 'WESTWOOD' AND (OFF_CODE = 'SICK') OR (OFF_CODE = 'FAMILY MEMBER SICK')

  The above will return 0 records, however if I change your help to

  SELECT *

  FROM TIME_OFF_CODES

  WHERE LOCATION = 'SOUTH'

  AND AREA = 'WESTWOOD' AND (OFF_CODE = 'SICK' OR OFF_CODE = 'FAMILY MEMBER SICK')

  I get the results I need.

  Once again I thank you so much for your help, and hope that your help and my clarification helps other as well.

  Thanks

  Best regards,

  Carlos

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  April 15, 2013 at 3:42 pm

  #1606636

  carlos.antunez (4/15/2013)

  ---

  Thanks for the help

  I guess that I should put down the entire query.

  Your help was great and helped me out to accomplish, however, when the entire query was structured, your help didn't work, and it wasn't because of you.

  Let me show you the entire query and also show you why it didn't work, and also what I did so that it could work

  SELECT *

  FROM TIME_OFF_CODES

  WHERE LOCATION = 'SOUTH'

  AND AREA = 'WESTWOOD' AND (OFF_CODE = 'SICK') OR (OFF_CODE = 'FAMILY MEMBER SICK')

  The above will return 0 records, however if I change your help to

  SELECT *

  FROM TIME_OFF_CODES

  WHERE LOCATION = 'SOUTH'

  AND AREA = 'WESTWOOD' AND (OFF_CODE = 'SICK' OR OFF_CODE = 'FAMILY MEMBER SICK')

  I get the results I need.

  Once again I thank you so much for your help, and hope that your help and my clarification helps other as well.

  Thanks

  Best regards,

  Carlos

  The difference is in how you're using the parentheses within the WHERE clause. When 'AND' and 'OR' operators are combined, there is a logical precedence that SQL uses to evaluate the full expression.

  See here for detail and examples:

  http://msdn.microsoft.com/en-us/library/ms186992(v=sql.105).aspx

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho