How to filter rows from a table based on "If at least one common value in list of string and a column that has list of string"

Question

I have one string students = "Ram,Jack,jim.. and so on";

I have one table called sports_group which is as below:

---

Sport_id	name	school	city
Fb100	Atul,Mani,Ram,Gopal	St. marry	Delhi
Cri101	Jack,Marry,Kim	Gschool	Delhi
Bdm102	Alex,Rex	NTschool	mumbai

I have to list all the rows in which name column and students string have atleast one common value. So Desired output should be:

{
    “Sport_id”:” Fb100”,
    “name”,” Atul,Mani,Ram,Gopal”,
    “school”,” St. marry”,
    “city”,” Delhi”
},

{
    “Sport_id”:” Cri101”,
    “name”,” Jack,Marry,Kim”,
    “school”,” Govt. school”,
    “city”,” Delhi”
}

What I am doing in my spring data repository class :

@Query(value = "select * from sports_group  g where FIND_IN_SET(?1, g.name)<>0  order by g.modifiedtime desc", nativeQuery = true)
Slice<SprtsGroup> searchByNames(String students, Pageable pageable);

But this is working only if String students has only one value e.g.:

 String students = “Ram”

But this is not working only if String students has more than one value e.g.:

 String students = “Ram,Kim”

Can anyone please help me how can I improve my native query to get desired result. Any suggestion or help is greatly appreciated. Thanks in advance!

Note: there are similar threads exist but I could not reach to the correct solution

Answer 1

You can modify your query to something like this:

@Query(value = "SELECT * FROM sports_group g WHERE EXISTS "
             + "(SELECT 1 FROM (SELECT DISTINCT name FROM sports_group) AS names "
             + "WHERE FIND_IN_SET(names.name, ?1) <> 0 AND FIND_IN_SET(names.name, g.name) <> 0) "
             + "ORDER BY g.modifiedtime DESC",
        nativeQuery = true)

This way your using the FIND_IN_SET method for each name