New to SQL Queries - 'Select the values that are possible but not present'

• acarmon02

  Grasshopper

  Points: 12

  More actions

  October 29, 2018 at 8:16 am

  #378407

  Afternoon all,

  Please can anyone help me with a Microsoft SQL Server query?

  I have set a constraint against a particular column which only allows the values of 1-100 to be entered.

  I would like to write a query against that column which basically says:

  'Show me a list of values between 1-100 which are not present within that column'

  I have the full scripts to create and populate the database if that helps anyone.

  Thanks

• Jonathan AC Roberts

  SSCoach

  Points: 18378

  More actions

  October 29, 2018 at 8:27 am

  #2011529

  acarmon02 - Monday, October 29, 2018 8:16 AM

  Afternoon all,

  Please can anyone help me with a Microsoft SQL Server query?

  I have set a constraint against a particular column which only allows the values of 1-100 to be entered.

  I would like to write a query against that column which basically says:

  'Show me a list of values between 1-100 which are not present within that column'

  I have the full scripts to create and populate the database if that helps anyone.

  Thanks

  ;WITH A(A) AS (SELECT '' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)),
  B(N) AS (SELECT TOP(100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM A A,A B)
SELECT B.N
FROM B
WHERE NOT EXISTS(SELECT * FROM myTable X WHERE X.myCol = B.N)

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 29, 2018 at 9:56 am

  #2011548

  Same Idea as Jonathan's query, I just prefer this for readability (which is subjective :))

  
Select possibleValue from mydb.dbo.PossibleValuesTable
EXCEPT
select actualValue from mydb.dbo.ActualValuesTable


  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

• Jeff Moden

  SSC Guru

  Points: 1004439

  More actions

  October 29, 2018 at 9:34 pm

  #2011635

  Matt Miller (4) - Monday, October 29, 2018 9:56 AM

  Same Idea as Jonathan's query, I just prefer this for readability (which is subjective :))

  
Select possibleValue from mydb.dbo.PossibleValuesTable
EXCEPT
select actualValue from mydb.dbo.ActualValuesTable


  More of the same idea...
  
 SELECT PossibleValue = N FROM dbo.fnTally(1,100)
 EXCEPT
 SELECT ActualValue FROM dbo.ActualValueTable
;


  Code for dbo.fnTally is attached (heh... seriously?  An SQL Server forum that won't allow attachment of files that have a .SQL extension)

  --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)