CODEWITHSUNDEEP
sqlsql-server
Dharmendra Prajapati
Dharmendra Prajapati

Reputation: 483

Selecting rows from sql table having multiple values in the field

Here i have a table 'tbllead' which is having column'leademployees' which contains the values such as

(450,449,451)- row1 
(450,449,451)-row 2
(455,449,454,451)- row3
(450,449,451)-row 4 
(450)-row5 )

then how to get the rows if my query is

select *
from TblLead
where LeadEmployees=451

enter image description here

The desired result should be... row 25,26,27,28,29,31,32,33,34,35

Upvotes: 0

Views: 45

Answers (2)

Artier
Artier

Reputation: 1673

It is very common scenerio where employee manage employees erds easily available. May be this is helpfull enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You should fix your data model. Storing lists of numbers as strings SQL is wrong:

  • Values should have the right data type
  • Foreign key relationships should be properly defined
  • SQL has poor string functions
  • Such queries cannot be optimized very well

Sometimes, you are stuck with someone else's really bad design decisions. Here is one thing you can use like:

where ',' + cast(LeadEmployees as varchar(255)) + ',' like '%,' + '451' + ',%'

Upvotes: 2

Related Questions