help in sql command .. i want list names which have defined id s.......

Question

hello

i have table consist of columns : id,name,job

and i have row stored with this data :

id: 1
name : jason
job: 11,12


id: 2
name : mark
job: 11,14

i want write sql command to fetch names which have value "14" stored in job column only from this table

so how i do that ?

thanks

Answer 1

SELECT 
       *
FROM   
       MyTable
WHERE 
       job LIKE '14,%' OR job LIKE '%,14' OR job LIKE '%,14,%'

EDIT: Thanks to onedaywhen

SELECT 
       *
FROM   
       MyTable
WHERE 
       (',' + job + ',') LIKE ('%,14,%')

Answer 2

You can do:

WHERE FIND_IN_SET(14, job)

But that is really not the correct way. The correct way is to normalize your database and separate the job field into its own table. Check this answer for extra information:

http://stackoverflow.com/questions/3870878/php-select-row-from-db-where-id-is-found-in-group-of-ids/3870914#3870914

Answer 3

You shouldn't be storing multiple job ids in the same field. You want to normalise your data model. Remove the 'job' column from your names table, and have a second JOB table defined like this:

id | name_id | job_id

 1    1         11
 2    1         12
 3    2         11
 4    2         14

where name_id is the primary id ('id') of the entry in the names table.

Then you can do:

SELECT name_id, job_id FROM JOB WHERE name_id = 1;

for example. As well as making your data storage far more extensible - you can now assign unlimited numbers of job_ids to each name for example - it'll also be much faster to execute queries as all your entries are now ints and no string processing is required.