My table id and skillid values are
1=>3
2 =>5
3 =>5,6,8
I have tried the query to fetch the value from my table
SELECT * FROM mytable WHERE skillid IN ('3', '5');
But i am getting only two rows, that was first and second row.
But i need the result three rows.
Thanks in advance.
The 'IN' comparison operator works like an equality operator. This:
expr IN ('3','5')
is equivalent to:
( expr = '3' OR expr = '5' )
That should be sufficient to explain why your query is not returning what you expect.
If your table has a character column that contains a comma separated list, like this:
id skill_id_list
-- -------------
1 3
2 5
3 5,6,8
Then one option, to search for a particular id value in the list is to make use of the MySQL FIND_IN_SET function. For example:
SELECT t.id
, t.skill_id_list
FROM mytable t
WHERE FIND_IN_SET('3', t.skill_id_list)
OR FIND_IN_SET('5', t.skill_id_list)
The MySQL FIND_IN_SET function is documented here:
https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set
Of course, that's only one option. There are several other ways to get an equivalent result.
If there are no spaces in the skill_id_list column, then another option is to use a LIKE operator, but taking care to handle the edge cases.
There are four cases you would need to check for the id value appearing:
To explicitly handle those four cases, you could check each one, for example:
WHERE skill_id_list LIKE '3' -- only one in list
OR skill_id_list LIKE '3,%' -- beginning of list
OR skill_id_list LIKE '%,3,%' -- middle of list
OR skill_id_list LIKE '%,3' -- end of list
Or, another way to approach that is to turn all of those cases into the single "middle of the list" by just appending a comma to the beginning and end of the list, and then doing a single check, for example:
WHERE CONCAT(',',skill_id_list,',') LIKE '%,3,%'
Note that spaces embedded in the list could cause a row not to match. The MySQL REPLACE function can be used to remove spaces, replacing all spaces with an empty string, something like this:
WHERE CONCAT(',',REPLACE(skill_id_list,' ',''),',') LIKE '%,3,%'
NOTE
The preceding attempts to to answer the question you asked. The following addresses a fundamentally different (though closely related) issue, concerning the relational dataa model and normalization.
Repeating attributes in normalized relational model are represented in separate table. That's the normative relational model. We typically avoid storing comma separated lists, and instead implement a separate table to store the repeating attributes.
For example, if a job has multiple skills, we would typically create another table to hold the list of job skills:
CREATE TABLE job
( id INT NOT NULL PRIMARY KEY
, description VARCHAR(10)
);
CREATE TABLE job_skill
( job_id INT NOT NULL COMMENT 'FK ref job.id'
, skill_id INT NOT NULL COMMENT ''
, PRIMARY KEY (job_id, skill_id)
, FOREIGN KEY job_skill_FK1 (job_id) REFERENCES job (id)
);
We'd represent the data in your model as five separate rows:
job_skill
job_id skill_id
------ --------
1 3
2 5
3 5
3 6
3 8
That's the normative pattern.
To get back a list of job ids that require skills 3 or 5:
SELECT s.job_id
FROM job_skill s
WHERE s.skill_id in (3,5)
GROUP BY j.job_id
To get back a list of job ids that require skills 3 and 5, there's several ways to do that, for example:
SELECT s.job_id
FROM job_skill s
ON s.job_id = t
WHERE s.skill_id in (3,5)
GROUP BY j.job_id
HAVING COUNT(DISTINCT s.skill_id) = 2
If it's more convenient for your use case to get back a comma separated list as a string, you can use the GROUP_CONCAT aggregate function:
SELECT j.id AS job_id
, GROUP_CONCAT(s.skill_id ORDER BY s.skill_id) AS skill_id_list
FROM job j
LEFT
JOIN job_skill s
ON s.job_id = j.id
WHERE ...
ORDER BY ...
Please check
SELECT * FROM mytable WHERE 5 IN (skillid) OR 3 IN (skillid);
SELECT * FROM mytable WHERE skillid LIKE '3%' OR skillid LIKE '5%';
Definetly this is NOT the way to do it...
YOU SHOULD NORMALIZE your table to avoid this kind of problems
This is not good practice, you should normalize your table table, but REGEXP would be helpful
Please try this way
SELECT * FROM mytable WHERE
skillid REGEXP '[[:<:]]3[[:>:]]' OR skillid REGEXP '[[:<:]]5[[:>:]]'
