Calculating Machine Calculating Machine - 3 months ago 10
MySQL Question

Mysql like query in inner join

I have a mysql query but it is returning 0 records. I am not sure why it is returning zero record.

Here is MySql query.

SELECT a.skills_name
FROM skills a inner join job b
ON a.skills_name like b.keywords
GROUP BY a.skills_name
ORDER BY a.skills_name ASC limit 15


Data in skills table is like this.

skills_id skills_name
1 It support
2 IT technician
4 sales


Data in Job table is like this.

sales, marketing
IT Support, IT Head


Please advise.

Answer

Using LIKE without placeholders ("%") is the same as using =. If your pseudocode for the JOIN condition is b.keywords CONTAINS a.skills_name then you need b.keywords LIKE coancat('%', a.skills_name, '%')

SELECT a.skills_name 
FROM skills a inner join job b 
    ON b.keywords LIKE coancat('%', a.skills_name, '%')
GROUP BY a.skills_name 
ORDER BY a.skills_name ASC limit 15

As other already mentioned you can use the DISTINCT keyword to remove duplicates instead of GROUP BY - (SELECT DISTINCT a.skills_name)

You should also consider to use a normalized schema instead of storing multiple values in a string column. In your case the job table would look like:

id | keyword
----------------
 1 | sales
 1 | marketing
 2 | IT Support
 2 | IT Head

Then your query would be:

SELECT DISTINCT a.skills_name 
FROM skills a inner join job b 
    ON b.keyword = a.skills_name
ORDER BY a.skills_name ASC limit 15