Still get status - Y

• GG_BI_GG

  SSCrazy

  Points: 2627

  More actions

  March 24, 2014 at 4:43 am

  #286905

  Hi SQL-GURU,

  I just make a simple qeury as following;

  select inc_cat.inc_cat_sc, inc_major.inc_major_sc, inc_cat.stat_flag

  from inc_cat

  INNER JOIN inc_major

  ON inc_major.inc_major_id = inc_cat.inc_major_id

  WHERE inc_major.inc_major_sc in ('incident','rfc')

  AND inc_cat.stat_flag != 'y'

  AND inc_cat.inc_cat_sc LIKE 'STCD%'

  or inc_cat.inc_cat_sc LIKE 'ID%'

  OR inc_cat.inc_cat_sc LIKE'RFCMD%'

  order by inc_cat_sc

  But I get in my results still the stat_flag Y.. Why I want only the status_flag N (I just inc_cat.stat_flag = 'n') But get the same results.

  Greetz

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  March 24, 2014 at 4:51 am

  #1699458

  You're mixing up ANDs and ORs without having any brackets to specify the precedence of the operators. Unless otherwise specified (by brackets) AND takes precedence over OR

  Without brackets, this is essentially what you are getting:

  select inc_cat.inc_cat_sc, inc_major.inc_major_sc, inc_cat.stat_flag

  from inc_cat

  INNER JOIN inc_major

  ON inc_major.inc_major_id = inc_cat.inc_major_id

  WHERE (inc_major.inc_major_sc in ('incident','rfc')

  AND inc_cat.stat_flag != 'y'

  AND inc_cat.inc_cat_sc LIKE 'STCD%')

  or inc_cat.inc_cat_sc LIKE 'ID%'

  OR inc_cat.inc_cat_sc LIKE'RFCMD%'

  order by inc_cat_sc

  So your filter for status of Y is only applying when the sc is LIKE STCD%

  Whereas I suspect what you really want is this:

  select inc_cat.inc_cat_sc, inc_major.inc_major_sc, inc_cat.stat_flag

  from inc_cat

  INNER JOIN inc_major

  ON inc_major.inc_major_id = inc_cat.inc_major_id

  WHERE inc_major.inc_major_sc in ('incident','rfc')

  AND inc_cat.stat_flag != 'y'

  AND (inc_cat.inc_cat_sc LIKE 'STCD%'

  or inc_cat.inc_cat_sc LIKE 'ID%'

  OR inc_cat.inc_cat_sc LIKE'RFCMD%')

  order by inc_cat_sc

  Anytime you have both ANDs and ORs in a where clause, put brackets to define which predicates have precedence.

  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