CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sandeep Thomas
Sandeep Thomas

Reputation: 4759

In MSSQL filter rows based on an ID exists in a column as comma separated string

I've Benchmarking table like this

BMID      TestID     BMTitle    ConnectedTestID
---------------------------------------------------
1          5         My BM1          0
2          6         My BM2          5
3          7         My BM3          5,6
4          8         My BM4          10,12,8
5          9         My BM5          0
6          10        My BM6          3,6
7          5         My BM7          8,3,12,9
8          3         My BM8          7,10
9          8         My BM9          0
10         12        My BM10         9
---------------------------------------------

Explaining the table a little

Here the TestID and the connected TestID is playing the roles. If the user wants all the benchmarks for the TestID 3

It should return rows where testID=3 and also if any rows having connectedTestID column having that testID in it among the comma separated values

That means if the user specify the value 3 as the testID, it should return

---------------------------------------------
8          3         My BM8          7,10
7          5         My BM7          8,3,12,9
6          10        My BM6          3,6
--------------------------------------------

Hope its clear how those 3 rows returned. Means First row is because the testID 3 is there. the other two rows because 3 is in their connectedIDs cell

Upvotes: 2

Views: 490

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You should fix the data structure. Storing numeric ids in a comma-delimited list is a bad, bad, bad idea:

  • SQL Server doesn't have the best string manipulation functions.
  • Storing numberings as character strings is a bad idea.
  • Having undeclared foreign key relationships is a bad idea.
  • The resulting queries cannot make use of indexes.

While you are exploring what a junction table is so you can fix the problem with the data structure, you can use a query such as this:

where testid = 3 or
      ',' + ConnectedTestID + ',' like '%,3,%'

Upvotes: 5

Related Questions