CODEWITHSUNDEEP
mysqlsql
Roxx
Roxx

Reputation: 3986

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.

Upvotes: 0

Views: 1095

Answers (3)

Paul Spiegel
Paul Spiegel

Reputation: 31772

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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

If you job table column for join is keywords Seems you don't need like and group by (use distinct if you don't have aggregation function)

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

Upvotes: 1

Gilad Green
Gilad Green

Reputation: 37299

Your on clause is wrong. The like keyword doesn't say "is it similar" but "does it contain X" (and using the % allows you to specify that it doesn't matter what comes after and/or before)

In addition, there is no reason for the group by. The use of the group by is when you want to perform some aggregation functions like sum/count. I assume you are using it here to get each value once. For that use distinct

Use this:

SELECT a.skills_name 
FROM skills a 
join job b 
on lower(b.keywords) like ('%' + lower(a.skills_name) + '%')
ORDER BY a.skills_name
limit 15

Upvotes: 0

Related Questions