Conversion failed when converting the varchar value 'Assistant' to data type int. SQL Mess-CodePudding

I have this query :

--What is the Specialization of employee ID 5?

SELECT EmpName
FROM Employee
WHERE Employee.ID IN(
SELECT Specialization
FROM Specialization
WHERE spe_mng_id = 5);

And when I run the command it returns me this error message: "Conversion failed when converting the varchar value 'Assistant' to data type int."

My records are in varchar :

--Table Specialization

create table Specialization(
spe_id int,
Specialization varchar(50),
spe_mng_id int IDENTITY(1,1),
FOREIGN KEY(spe_mng_id) REFERENCES Employee(ID)
);

I don't understand what is hapenning, I am also quite new to SQL so it's dificult to find answers for my exact problem, thus asking the comunity!

I am expecting to get a return value of the Specialization of Employee with ID 5.

ID and spe_mng_id are linked.

CodePudding user response：

When you run SELECT Specialization FROM Specialization WHERE spe_mng_id = 5, you already get a list of all specializations (possibly only one) of employee n5. If this is the only information needed (as in nothing from table Employee is required), you could even run this query directly.

However, if you also need to get the EmpName as an output (or any other info from table Employee), you will need to join the two tables using the JOIN command, using the info that spe_mng_id is a reference to ID in the table Employee (as seen through the FOREIGN KEY definition).

Something like:

SELECT EmpName, Specialization
FROM Employee
JOIN Specialization ON Employee.ID = Specialization.spe_mng_id
WHERE Employee.ID = 5

The conversion problem happened because you were trying to find rows whose Employee.ID (an INT) was in the list of Specialization of Employee n5 (a column of VARCHAR).

CodePudding user response：

Thank you for the help I got it to work and I understood the problem, I now have some dificulty understanding this:

SELECT EmpName, Specialization FROM Employee JOIN Specialization ON Employee.ID = Specialization.spe_mng_id WHERE Employee.ID = 5

The query is saying : Select empname and specialization from Employee(But employee table does not have the column " spe_mng_id ")

Is the Join there to specify that it can also be FROM Specialization?

This part I understand it joins 2 columns from different tables. JOIN Specialization ON Employee.ID = Specialization.spe_mng_id