CODEWITHSUNDEEP
sqlsql-servert-sql
MIS_Gem
MIS_Gem

Reputation: 117

Selecting records based on multiple criteria in a single cell

I am working with a database that is set up to show multiple values, separated by commas as shown:

txtSiblingsYearList
7,4
9,6
8,3,N
8,3,N
5,3
5,3

I need to be able to query this and only bring back records that have at least 2 of R, N, 1, 2, 3, 4, 5, 6

I am not sure if this is something that can be done given the way the data is stored within the database table. Does anyone know a way to do this. My current query looks like:

    SELECT 
      [txtSchoolID]
      ,[txtTitle]
      ,[txtForename]
      ,[txtSurname]       
      ,[txtForm]
      ,[intNCYear]      
      ,[intFamily]      
      ,[txtSiblingsIDList]
      ,[txtSiblingsNameList]
      ,[txtSiblingsFormList]
      ,[txtSiblingsYearList]      
  FROM [iSAMS].[dbo].[TblPupilManagementPupils]
  Where (intSystemStatus = 1) 
  AND (intNCYear <7) 
  AND (txtSchoolID NOT LIKE txtSiblingsIDList)  
  Order By intFamily

Any help appreciated.

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You should not be storing lists of values in a comma-delimited string. That is not the SQL way of doing things. The resulting queries cannot be optimized, and string functions are not SQL's forte.

If you are stuck with this format, you can add up the number of matches:

where ( (case when ',' + txtSiblingsYearList + ',' like '%,R,%' then 1 else 0 end) +
        (case when ',' + txtSiblingsYearList + ',' like '%,N,%' then 1 else 0 end) +
        (case when ',' + txtSiblingsYearList + ',' like '%,1,%' then 1 else 0 end) +
        . . .
      ) >= 2

Upvotes: 3

Related Questions