0

How can I write an MySQL query to fetch the data from database based on comma separated values provided at runtime? PS: ignoring order of precedence for comma separated values.

I have tried with Like operator but it didn't gave me accurate result. Also tried IN operator but it didn't work at all.

SELECT * FROM Candidate_Table WHERE Key_Skills LIKE ? AND Key_Skills LIKE ? AND Key_Skills LIKE ?;

Expected Result: the query should result accurate values that matches the input values. Input values: Java, C, HTML5

F_Name L_Name Mobile Key_Skills Experience City

---------- ---------- ---------- ---------- ---------- ----------

Tim Johnson 5525365 C, Java, Python, HTML5 3 years Orlando

Mayukh Mitra
  • 39
  • 1
  • 4

3 Answers3

0

try This

SELECT F_Name,L_Name,Mobile ,concat(Key_Skills ,",",Key_Skills ),Experience,City
FROM Candidate_Table group by F_Name,L_Name,Mobile ,Experience,City;

Govind Sharma
  • 127
  • 1
  • 4
0

You may try putting the key skills into an array , say keyskills and do

SELECT * from Candidate_Table where Key_Skills in (keyskills);

You may replace * with the columns you want to display and group by those columns

G. Pillai
  • 237
  • 1
  • 7
0

That sounds like a job for a Full Text Search, you'll have to define a FULLTEXT index for the Key_Skills field. This kind of index allows you to do this kind of searches:

SELECT * FROM Candidate_table WHERE MATCH(Key_Skills) AGAINST('Java Python'  IN NATURAL LANGUAGE MODE)

The result will include all the Candidates that have those skills. There is also a mode where you can do more specific search, for example, if you want all candidates that have Java and Python, but excluding those who have PHP, you can do a BOOLEAN search, like you do on Google.

SELECT * FROM Candidate_table WHERE MATCH(Key_Skills) AGAINST('+Java +Python -PHP'  IN BOOLEAN MODE)

All you have to do is arrange your search terms using the operators provided. Here are more examples on this: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

Kenrro
  • 46
  • 3
  • Thank you for helping me @Kenrro. Is it though possible to use placeholder '?' inside AGAINST in place of 'Java Python'? – Mayukh Mitra Jun 11 '19 at 04:37
  • What programming language or framework are you using? – Kenrro Jun 11 '19 at 12:06
  • I am using Java, Kenrro. – Mayukh Mitra Jun 18 '19 at 06:41
  • Hey @Kenrro, I have used your query and it is giving the positive result. Is it however possible with your query that only those rows from database should be fetched that matches with all the values provided as input. For example if I provide input like 'HTML5, CSS3, JavaScript' then the rows which contains only these three values in Key_Skills column should be fetched. – Mayukh Mitra Jun 18 '19 at 12:25