INNER JOIN question

Databases
1

Hi

I have the following INNER JOIN in one of my select queries, but it only fetches the ID of the industry. I need to get the industry name which is stored in another table (“industries”). How can I do that?

INNER JOIN (
	SELECT user_id, group_concat( industry_id
	SEPARATOR ',' ) AS industry
	FROM users_industries
	GROUP BY user_id
) AS i ON i.user_id = users.id

Thanks in advance.

2

join the two tables

3

using a left join within the inner join?

4

by asking this question, you reveal either mistrust of your own data, or misunderstanding of outer joins

i’m going to guess that there won’t be any industry_id values in the user_industries table that don’t also exist in the industries table, and therefore an outer join is not warranted

that guess will be an absolute certainty if you have actually declared foreign keys in the user_industries table

5

Here is the whole query:. I’m at a loss of how to make this happen :frowning:


SELECT 
	users.id, 
	users.first_name, 
	users.last_name, 
	users.dob, 
	users.address_1, 
	users.address_2, 
	users.city, 
	users.county AS country, 
	users.telephone, 
	users.email, 
	users.username, 
	users.language, 
	i.industry, 
	j.job_type, 
	users.cv

FROM users

INNER JOIN (
	SELECT user_id, group_concat( industry_id
	SEPARATOR ',' ) AS industry
	FROM users_industries
	GROUP BY user_id
) AS i ON i.user_id = users.id

INNER JOIN (
	SELECT user_id, group_concat( job_type_id
	SEPARATOR ',' ) AS job_type
	FROM users_job_types
	GROUP BY user_id
) AS j ON j.user_id = users.id

ORDER BY users.signup_date DESC
6 
INNER JOIN (
  SELECT ui.user_id
       , GROUP_CONCAT( 
            CONCAT(ui.industry_id,';',i.name)
            SEPARATOR ',' ) AS industry
    FROM users_industries AS ui
  INNER
    JOIN industries AS i
      ON i.id = ui.industry_id
  GROUP 
      BY ui.user_id
           ) AS ind ON ind.user_id = users.id

the subquery now returns a string of ids and names like this –

9;mining,3;farming,7;fishing

notice the two different separators and where they came from

you’ll probably also want to do the same with job types, yes?

p.s. you did understand what i said about foreign keys, right?