0

within my database table, i have a sector field that contains a comma separated list of sectors. each row pertains to a different employee so each sector set is going to be different. for example

employee | sectors
---------|--------
john     |commercial
sam      |commercial,education
bill     |education,government
tom      |government
sarah    |commercial,utilities
-------------------------------

what im trying to do is formulate a mysql query expression that will return a list of employee names that contain a sector that is within a list i provide

let say my list of sectors is ['commercial', 'government'] and i want to query the database to return all employee names that have at least one of these sectors. so in this case i would get every employee name because the all have at least one sector that matches the two in my list, however if i change commercial to education within my list, the names i would then get would be sam, bill, tom.

if i was to do this in JS, it would look something along the lines of this.

var list = ['commercial', 'government']
for(var i=0; i<sectors.length, i++){
   if(sectors[i] in list) {
      return employee
   }
}
wmunsell
  • 23
  • 5
  • https://stackoverflow.com/a/1865377/6770704 – Ian Aug 03 '20 at 18:15
  • @lan - this would work fine if there was only one sector value for each employee however there are sometimes multiple. – wmunsell Aug 03 '20 at 18:33
  • just chain all the selector values in the where condition: SELECT employee FROM TABLE_NM WHERE sectors LIKE '%value1%' OR sectors LIKE '%value2%' OR sectors LIKE '%value3%'... – Ian Aug 03 '20 at 18:38
  • 1
    Storing a comma-separated list of values in a column is almost always a sign of a poor database design. – Honeyboy Wilson Aug 03 '20 at 18:39
  • 1
    There is `FIND_IN_SET()` but it's *brutally slow* compared to restructuring this to be a proper relational design. – tadman Aug 03 '20 at 19:20

0 Answers0