trouble with inner joining 2 tables-CodePudding

I have a database with 2 tables in it one is 'enlistments' and the other one is 'users'. In the enlistments table I have a user_id and in the users table I have a name. I want to get the name of the user which belongs to the id.

I know I need to do this with an inner join like this:

SELECT enlistments.round_id, users.name
FROM enlistments
INNER JOIN users
ON enlistments.user_id=users.name
WHERE enlistments.activity_id = 1;

However I get this error: Warning: #1292 Truncated incorrect DOUBLE value

I did some research and found out it has to do with comparing an int with a string but I don't know how to solve the problem.

This is how my database looks like

CodePudding user response：

join on is the condition you use to join the tables. Here it's enlistments.user_id=users.id.

select  e.round_id
       ,u.name
from    enlistments e join users u on u.id = e.user_id
where   activity_id = 1

round_id	name
1	test2

Fiddle

CodePudding user response：

To validate and be sure you are pulling back the exact data desired, I usually provide aliases for each column brought back and make sure to bring back the join columns also. It's good practice to label where the columns returned originated.

SELECT 
Enlistments.UserID as Enlistments_UserID,
Users.ID as Users_ID,
enlistments.round_id as Enlistments_RoundID, 
users.name as Users_Name
FROM enlistments
INNER JOIN users
ON enlistments.user_id=users.id
WHERE enlistments.activity_id = 1;

CodePudding user response：

SELECT EN.round_id, US.name
FROM enlistments EN
INNER JOIN users US 
        ON US.name= CAST(EN.user_id AS VARCHAR) 
WHERE EN.activity_id = 1

WHAT'S YOU ARE NEEDING IS THE FUNCTION CAST THAT CAN CONVERT ANY KIND OF DATA INTO ANOTHER SO YOU'LL PASS YOUR INTEGER VALUE AS THE FIRST ARGUMENT FOLLOWED BY "AS '