How to join tables based on ids and names with multiple values

Hamza Khalid 0
Viewed 33

I have 3 mysql tables:

Users:

id | name | interest | user_id

1  | user1 | 1,2  |  1

2  | user2 | 1,2,3  |  2

Interests:

id | name

1  | interest1

2  | interest2

User_posts:

id | user_id | desc

1  | 23  | something..

2  | 31  | something..

What i want to achieve is i want join interest column from users table into user_posts table based on user_id with user_name from interests table which i have already done using this query:

select user_posts.*, users.interest as interest_ids, zaya.interests.name as interest_name 
from user_posts 
left join users ON user_posts.id = users.interest
left join interests ON user_posts.id = users.interest;

i get the following output with this query:

User_posts:

id | user_id | desc | interest_ids | interest_name

1  | 23  | something.. | 1,2  | interest1 

2  | 31  | something.. | 1,2,3 | interest1

Output i want to achieve:

User_posts:

id | user_id | desc | interest_ids | interest_name

1  | 23  | something.. | 1,2  | interest1, interest2

2  | 31  | something.. | 1,2,3 | interest1, interest2, interest3

I believe this can be solved using the temporary table creation method and have tried solving this but i'm newbie in mysql queries and getting errors please help me with the correct query. Thank you in advance.

left-join join mysql sql
1 Answers

I would use a sub query rather than a join if you're looking to just get back one col form the [interests] table. Here is an example using STRING_SPLIT function to get the names :

DECLARE @users TABLE(
    [ID] int IDENTITY(1,1) 
    ,[NAME] varchar(32) 
    ,[INTERESTS] varchar(32) 
)
INSERT INTO @users 
    VALUES ('user1', '1,2'), ('user2', '1,2,3') 

DECLARE @interests TABLE(
    [ID] int IDENTITY(1,1) 
    ,[NAME] varchar(32) 
)
INSERT INTO @interests 
    VALUES ('interests1'), ('interests2'), ('interests3') 

DECLARE @user_posts TABLE(
    [ID] int IDENTITY(1,1) 
    ,[USERID] int 
    ,[DESC] varchar(32) 
)
INSERT INTO @user_posts 
    VALUES (1, 'post1'), (2, 'post2') 

SELECT 
    U.[ID] 
    ,UP.[USERID] 
    ,UP.[DESC] 
    ,U.[INTERESTS] 
    ,SUBSTRING((SELECT ',' + I2.[NAME] 
    FROM @interests I2 
    WHERE I2.[ID] IN (SELECT [name] FROM STRING_SPLIT(U.[INTERESTS], ',')) 
    FOR XML PATH('')), 2, 1000) AS 'INTEREST_NAME' 
FROM @user_posts UP 
LEFT JOIN @users U 
    ON U.[ID] = UP.[USERID]

This should return :

ID USERID DESC INTERESTS INTEREST_NAME
1 1 post1 1,2 interests1,interests2
2 2 post2 1,2,3 interests1,interests2,interests3
Hilton Siegert0
Related