How to join 2 tables with one of them containing multiple values in a single column

Question

I have two tables users and interests which i'm trying to join. Inside users table i have columns as id, name, interest, etc. The interest column contain multiple values as "1,2,3". My second table interests have 2 columns id and name as:

id | name
-------------
1  | business  
2  | farming 
3  | fishing

What i want to do is join interests table with users table so i get the following output:

users table:

id | name     | interest | interest_name 
----------------------------------------------
1  | username | "1,2"    | "business, farming"
2  | username | "2,3"    | " farming, fishing"

I wrote the following query to achieve this:

select users.*, interests.name as interest_name
  from users
  left join interests on users.interest = interests.id;

Results i got:

id | name     | interest | interest_name
----------------------------------------
1  | username | "1,2"    | "business"
2  | username | "2,3"    | " farming"

Problem:

I'm only getting the name of first values from interest column whereas i want all the values from interest column i have already tried using group_concat and find_in_set but getting the same results.

bloodyKnuckles · Answer 1 · 2022-09-09T06:15:59.523

Pull the interest column out of the users table and create a user_interests table that contains the user ids and interest ids:

user_id | interest_id
--------+------------
1       | 1
1       | 2
2       | 2
2       | 3

Then join the users table to the user_interests table, and the user_interests table to the interests table:

SELECT users.username, interests.interest 
  FROM users 
  LEFT JOIN user_interests ON users.id = user_interests.user_id 
  LEFT JOIN interests ON user_interests.interest_id = interests.id 
  WHERE interest_id IS NOT NULL;

Outputs:

username | interest
---------+---------
Clark    | business
Clark    | farming
Dave     | farming
Dave     | fishing

Then use your server programming language to compile the query results.

Try it here: https://onecompiler.com/mysql/3yfe5pp7x

-- create
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL
);

CREATE TABLE user_interests (
  user_id INTEGER,
  interest_id INTEGER,
  UNIQUE KEY user_interests_constraint (user_id,interest_id)
);

CREATE TABLE interests (
  id INTEGER PRIMARY KEY,
  interest TEXT NOT NULL
);

-- insert
INSERT INTO users VALUES (1, 'Clark'), (2, 'Dave'), (3, 'Ava');
INSERT INTO interests VALUES (1, 'business'), (2, 'farming'), (3, 'fishing');
INSERT INTO user_interests VALUES (1, 1), (1, 2), (2, 2), (2, 3);

-- fetch 
SELECT users.username, interests.interest 
  FROM users 
  LEFT JOIN user_interests ON users.id = user_interests.user_id 
  LEFT JOIN interests ON user_interests.interest_id = interests.id 
  WHERE interest_id IS NOT NULL;

We are working with a pre-structured database and cannot change its structure at the moment. So will need a different solution thankyou for the response though. — Hamza Khalid, Sep 09 '22 at 04:19
The other reply you posted seemed like a solution for my problem. But you deleted it for some reason can you please re-post it? — Hamza Khalid, Sep 09 '22 at 07:13
It had an error. So I found the error and fixed—reposted. :) — bloodyKnuckles, Sep 09 '22 at 07:52

bloodyKnuckles · Accepted Answer · 2022-09-09T16:39:30.850

In the case you cannot create an additional database table in order to normalize the data...

Here's a solution that creates an ad hoc, temporary user_interests table within the query.

SELECT users.id user_id, username, interests, interests.interest
FROM users
LEFT JOIN (
  SELECT
    users.id user_id,
    (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
  FROM users
    LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
      ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)
) user_interests ON users.id = user_interests.user_id
LEFT JOIN interests ON user_interests.ui_id = interests.id
ORDER BY user_id, ui_id;

Outputs:

user_id | username | interest_ids | interest
--------+----------+--------------+---------
1       | fred     | 3,4,8,6,10   | fishing
1       | fred     | 3,4,8,6,10   | sports
1       | fred     | 3,4,8,6,10   | religion
1       | fred     | 3,4,8,6,10   | science
1       | fred     | 3,4,8,6,10   | philanthropy
2       | joe      | 7,11,8,9     | art
2       | joe      | 7,11,8,9     | science
2       | joe      | 7,11,8,9     | politics
2       | joe      | 7,11,8,9     | cooking

As you can see...

SELECT
  users.id user_id,
  (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
FROM users
  LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
    ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)

...builds and populates the temporary table user_interests with the users.interests field data normalized:

user_id | ui_id
--------+------
1       | 3
1       | 4
1       | 6
1       | 8
1       | 10
2       | 7
2       | 8
2       | 9
2       | 11

...which is then LEFT JOIN'ed between the users and interests tables.

Try it here: https://onecompiler.com/mysql/3yfhmgq3y

-- create
CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(20),
  interests VARCHAR(20)
);

CREATE TABLE interests (
  id INT PRIMARY KEY,
  interest VARCHAR(20)
);

-- insert
INSERT INTO users VALUES (1, 'fred', '3,4,8,6,10'), (2, 'joe', '7,11,8,9');
INSERT INTO interests VALUES (1, 'business'), (2, 'farming'), (3, 'fishing'), (4, 'sports'), (5, 'technology'), (6, 'religion'), (7, 'art'), (8, 'science'), (9, 'politics'), (10, 'philanthropy'), (11, 'cooking');

-- select
SELECT users.id user_id, username, interests, interests.interest
FROM users
LEFT JOIN (
  SELECT
    users.id user_id,
    (SUBSTRING_INDEX(SUBSTRING_INDEX(users.interests, ',', ui.ui_id), ',', -1) + 0) ui_id
  FROM users
    LEFT JOIN (SELECT id AS ui_id FROM interests) ui 
      ON CHAR_LENGTH(users.interests) - CHAR_LENGTH(REPLACE(users.interests, ',', '')) >= (ui.ui_id - 1)
) user_interests ON users.id = user_interests.user_id
LEFT JOIN interests ON user_interests.ui_id = interests.id
ORDER BY user_id, ui_id;

Inspired by Leon Straathof's and fthiella's answers to this SO question.

Thank you for the help looks like i'm almost there with your solution. One last thing what i would like to ask is there a way to make the output return single record with multiple interests instead of dublication like so: user_id | username | interest_ids | interest 1 | fred | 3,4,8,6,10 | fishing, sports, religion, science, philanthropy — Hamza Khalid, Sep 09 '22 at 10:28
Nevermind i solved it using group_concat. Thank you you're a legend. — Hamza Khalid, Sep 09 '22 at 10:56

How to join 2 tables with one of them containing multiple values in a single column

2 Answers2