MySQL Multiple Join with delimiting via FINDINSET

Question

I am attempting to JOIN onto two different columns in the first table below from columns in the second and third tables.

I wish to JOIN users.id to job_listings.id to return users.username, and to also JOIN and delimit job_listings.categories to job_categories.id to return job_categories.description via FIND_IN_SET

job_listings
id    |  employer_id  |  categories
1     |       1       |  1,2
2     |       1       |  2

users
id    |  username  |  type
1     |    foo     |  employer
2     |    wat     |  employer

job_categories
id    |  description
1     |     fun
2     |     hak

I desire output that is of the following format:

output
username  |    type    |  category  |  description
  foo     |  employer  |     1      |      fun
  foo     |  employer  |     2      |      hak
  foo     |  employer  |     2      |      hak

I have tried using various permutations of the following code:

SELECT users.username, users.type, job_listings.categories FROM users
JOIN job_listings ON users.id
JOIN job_listings AS category ON FIND_IN_SET(category.categories, job_categories.id)
ORDER BY users.username, category.categories

I know from other answers that I need to use an alias in order to use multiple JOIN operations with the same table, but despite adapting other answers I keep receiving errors related to declaring an alias, or returning output that has a column with the alias but no data returned in that column.

1

Normalise your design. – Strawberry Jun 21 '15 at 18:50

score 1 · Accepted Answer · answered Jun 21 '15 at 19:32

First, you should normalize your design. You should not store integer values in strings. You should not have foreign key references that you cannot declare as such. You should not store lists in strings. Is that enough reasons? You want a junction table for JobCategories with one row per job and one row per category.

Sometimes, we are stuck with other peoples lousy decisions and cannot readily change them. In that case, you want a query like:

SELECT u.username, u.type, jc.id, jc.category
FROM users u JOIN
     job_listings jl
     ON u.id = jl.employer_id and u.type = 'employer' join
     job_categories jc
     ON FIND_IN_SET(jc.id, j.categories) > 0
ORDER BY u.username, jc.category;

This query cannot take advantage of indexes for the category joins. That means that it will be slow. The proper data structure -- a junction table -- would fix this performance problem.

MySQL Multiple Join with delimiting via FINDINSET

1 Answers1