How to compare comma seperated two fields in mysql query

Question

I have two tables.

First table name 'student' contains field course which contains values like 1,45,89

Second table name 'package' contains field courseid which contains values like 1,5,89

I want to compare both fields. I want common values in these both fields. So to do this I have tried

SELECT 
   s.name, 
   p.name 
FROM 
   student AS s, 
   package AS p 
WHERE 
   find_in_set(s.course, p.courseid)

But resultset returns 0 records. Can anybody help me ... thank you in advance

Are you able to use a link table instead of the comma-separated list? http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574 — Matt Raines, Apr 29 '16 at 12:35
I have use this query : select * from tmp1 as t1 left join tmp2 as t2 on find_in_set(t1.comma, t2.chk) and another query is : select * from tmp1 as t1 left join tmp2 as t2 on t1.id=t2.id where find_in_set(t1.comma, t2.chk) But it returns null or empty result set — Shital Prajapati, Apr 29 '16 at 12:45
I'm not having much luck with generic solutions, but might try a solution that reaches up to 100 items in the comma-separated list. Do you have an idea about the maximum size of your list? — Tina Vall, Apr 29 '16 at 13:36

score 1 · Answer 1 · answered Apr 29 '16 at 12:39

find_in_set first parameter should by only one of the numbers, not whole set. so you can select all course ids in which you are interested and then check wether they exist in set of student table and package table.

BUT this way it is not good use of relation database, you should use mapping tables (ex.: student_map_course with columns id_student id_course) where you can have more rows for each student.

SECOND thing is, you can't select things this way, if you are using both tables (student and course) you have to use some sort of JOIN and in creating JOINs mapping tables would help you again (student_map_course for example), so you can join student by his id to courses where he attends..

score 0 · Answer 2 · edited May 23 '17 at 12:23

I would use PHP to do the comparison.

$a = "1,45,89";
$b = "1,5,89";

$a_arr = explode(",", $a);
$b_arr = explode(",", $b);

$values_in_both = array_intersect($a_arr, $b_arr);

var_dump($values_in_both);

There are possible SQL queries you can use, but the one I put together is really messy. I combined two answers to create it.

Kickstart's Split string and

FBB's Intersect

SELECT temp.a_course_id from (

(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(student.course, ',', sub0.aNum), ',', -1) AS a_course_id
FROM student
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10
AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0

ON (1 + LENGTH(student.course) - LENGTH(REPLACE(student.course, ',', ''))) >= sub0.aNum
GROUP BY a_course_id)

UNION ALL

(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(package.courseid, ',', sub0.aNum), ',', -1) AS a_course_id
FROM package
INNER JOIN
(
    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10
AS aSubscript
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0

ON (1 + LENGTH(package.courseid) - LENGTH(REPLACE(package.courseid, ',', ''))) >= sub0.aNum
GROUP BY a_course_id))
  AS temp GROUP BY a_course_id HAVING count(*) >= 2

You can probably reduce the clutter by saving the SELECT 0 from i UNION SELECT 1... portion, though I wanted to keep things close to the answers I referred to.

Code-only answers are not useful. What does the OP feed into this? What does the OP's query look like? This does not answer the OP's question about how to construct a *query* that does what they want.... — random_user_name, Apr 29 '16 at 12:46
Note: OP = "Original Poster" (the person asking the question) — random_user_name, Apr 29 '16 at 12:47

score 0 · Answer 3 · answered Apr 29 '16 at 13:15

Your table structure is not standard. Try to normalize your table which will help a lot. For current scenario: fetch data from 'student' table. Then split them & create dynamic string using loop (Like : 'package.course_id REGEXP '[[:<:]]".$course_id."[[:>:]]') & use.

foreach($arrCourseid as $courseid)
{
   $str .= strlen($str) > 0 ? ' OR ' : ' and (' ;
   $str .= ' package.course_id REGEXP '[[:<:]]".$course_id."[[:>:]]';

}
$str .= strlen($str) > 0 ? ' ) ' : '' ;

$mainqry = "SELECT s.name, p.name FROM student AS s , package AS p WHERE 1 " . $str ;

How to compare comma seperated two fields in mysql query

3 Answers3