Inside out on my Where clause logic

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  January 23, 2019 at 1:40 pm

  #416649

  WHERE

  (B.ELIGIBILITY_FLG <>'Y' or B.ELIGIBILITY_FLG is NULL)

  and DM.ELIGIBILITY_FLG <>'Y'

  B is Sol Tracker and DM is Duty Manager

  I want to see all rows where B is not Y or B is Null.

  But I don’t want to see any rows where DM is a Y

  Thoughts? thanks

• Phil Parkin

  SSC Guru

  Points: 246711

  More actions

  January 23, 2019 at 1:50 pm

  #2019868

  jeffshelix - Wednesday, January 23, 2019 1:40 PM

  WHERE

  (B.ELIGIBILITY_FLG <>'Y' or B.ELIGIBILITY_FLG is NULL)

  and DM.ELIGIBILITY_FLG <>'Y'

  B is Sol Tracker and DM is Duty Manager

  I want to see all rows where B is not Y or B is Null.

  But I don’t want to see any rows where DM is a Y

  Thoughts? thanks

  Should work. What are you seeing?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  January 23, 2019 at 1:52 pm

  #2019871

  thanks, I lose the NULL lines in B. I only get the N.

  (B is either Y, N or Null)  I don't want to see any of the Y's)

  Jeff

• ZZartin

  SSC-Dedicated

  Points: 30894

  More actions

  January 23, 2019 at 2:00 pm

  #2019872

  jeffshelix - Wednesday, January 23, 2019 1:52 PM

  thanks, I lose the NULL lines in B. I only get the N.

  (B is either Y, N or Null)  I don't want to see any of the Y's)

  Jeff

  What are the possible values in the other table DM.ELIGIBILITY_FLG when those NULL values show up in B?

  It's possible those are perfectly lining up.

• Phil Parkin

  SSC Guru

  Points: 246711

  More actions

  January 23, 2019 at 2:03 pm

  #2019873

  ZZartin - Wednesday, January 23, 2019 2:00 PM

  jeffshelix - Wednesday, January 23, 2019 1:52 PM

  thanks, I lose the NULL lines in B. I only get the N.

  (B is either Y, N or Null)  I don't want to see any of the Y's)

  Jeff

  What are the possible values in the other table DM.ELIGIBILITY_FLG when those NULL values show up in B?

  It's possible those are perfectly lining up.

  Have you tried 
  and isnull(DM.ELIGIBILITY_FLG,'N') <>'Y'
  ?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  January 23, 2019 at 2:09 pm

  #2019876

  You are joining two tables together some how but you haven't shown us the rest of the query.

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  January 23, 2019 at 2:14 pm

  #2019878

  DM table could be NULL, Y or N, independent of B.

  But if DM is a  Y, i don't care what B is.  and Vice Versa.
  I only want rows where B is not a Y AND B is not a y.
  but i like your idea of ISNULL...then N.   that seemed to help

  I get A LOT OF rows back, but that is on my side to see if the code is right.

  thanks

• Phil Parkin

  SSC Guru

  Points: 246711

  More actions

  January 23, 2019 at 2:18 pm

  #2019879

  jeffshelix - Wednesday, January 23, 2019 2:14 PM

  DM table could be NULL, Y or N, independent of B.

  But if DM is a  Y, i don't care what B is.  and Vice Versa.
  I only want rows where B is not a Y AND B is not a y.
  but i like your idea of ISNULL...then N.   that seemed to help

  I get A LOT OF rows back, but that is on my side to see if the code is right.

  thanks

  WHERE NOT (DM = 'Y' AND B = 'Y')

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 23, 2019 at 2:31 pm

  #2019881

  What if we work with some sample data.
  
CREATE TABLE SolTracker(
    eligibility_flg char(1) NULL
);

  CREATE TABLE DutyManager(
    eligibility_flg char(1) NULL
);

  INSERT INTO SolTracker VALUES('Y'),('N'),(NULL);
INSERT INTO DutyManager VALUES('Y'),('N'),(NULL);

  SELECT *
FROM SolTracker B
CROSS JOIN DutyManager DM
WHERE (B.ELIGIBILITY_FLG <>'Y' OR B.ELIGIBILITY_FLG is NULL)
and (DM.ELIGIBILITY_FLG <> 'Y' OR DM.ELIGIBILITY_FLG is NULL)

  GO
DROP TABLE SolTracker, DutyManager;


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jeffrey Williams

  SSC Guru

  Points: 90254

  More actions

  January 23, 2019 at 3:32 pm

  #2019887

  Phil Parkin - Wednesday, January 23, 2019 2:18 PM

  jeffshelix - Wednesday, January 23, 2019 2:14 PM

  DM table could be NULL, Y or N, independent of B.

  But if DM is a  Y, i don't care what B is.  and Vice Versa.
  I only want rows where B is not a Y AND B is not a y.
  but i like your idea of ISNULL...then N.   that seemed to help

  I get A LOT OF rows back, but that is on my side to see if the code is right.

  thanks

  WHERE NOT (DM = 'Y' AND B = 'Y')

  This won't work - it will return rows where either one of the values is 'Y' and the other is 'N'.  Switching to an OR removes the NULL values...

  I think Luis has the solution - which could be rewritten as:

  WHERE coalesce(DM, 'N') <> 'Y'
  AND coalesce(B, 'N') <> 'Y'

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Phil Parkin

  SSC Guru

  Points: 246711

  More actions

  January 24, 2019 at 10:29 am

  #2020008

  Jeffrey Williams 3188 - Wednesday, January 23, 2019 3:32 PM

  Phil Parkin - Wednesday, January 23, 2019 2:18 PM

  jeffshelix - Wednesday, January 23, 2019 2:14 PM

  DM table could be NULL, Y or N, independent of B.

  But if DM is a  Y, i don't care what B is.  and Vice Versa.
  I only want rows where B is not a Y AND B is not a y.
  but i like your idea of ISNULL...then N.   that seemed to help

  I get A LOT OF rows back, but that is on my side to see if the code is right.

  thanks

  WHERE NOT (DM = 'Y' AND B = 'Y')

  This won't work - it will return rows where either one of the values is 'Y' and the other is 'N'.  Switching to an OR removes the NULL values...

  I think Luis has the solution - which could be rewritten as:

  WHERE coalesce(DM, 'N') <> 'Y'
  AND coalesce(B, 'N') <> 'Y'

  My query was a direct response to this requirement from the OP:
  

  I only want rows where B is not a Y AND B is not a y.

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.