2

I inner join 2 table (Termination Reasons with Users) were the two talk to the each other and Termination Reasons table contains 2 column (Id (int) , Name(varchar)) and the other one , Users Table contains 1 column (EmploymentStatusId (int)) .

I can use EmploymentStatusId(int) from tbl Users with Id (int) from another table wich is Termination Reasons beacuse , both of them have same data type (int):

SELECT 
u AS Status

FROM 
d AS p INNER JOIN d AS u ON u=p
INNER JOIN d AS t ON t = u
WHERE u=214 order by u;

But when i try using EmploymentStatusId (int) from tbl Users with Name (varchar) from tbl Termination Reasons , i got problem:

SELECT
t AS Status

FROM 
d AS p INNER JOIN db AS u ON u.I=p
INNER JOIN b AS t ON t= u
WHERE u=214 order by u;

And then Error :
Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int.

i know why ,because they have different data type , therefore they can not talk to the each other.
but i tried also to solve this with Convert but idont know ,why or how is not working.Can someone please help or point me in the right direction. Thanks.
The Query ,where i tried with CONVERT ,its look like this:

SELECT
CONVERT(INT, CONVERT(VARCHAR(12), t)) as Status

FROM 
db AS p INNER JOIN d AS u ON u =p 
INNER JOIN db AS t ON t.Name = u
WHERE u=214 order by u.FirstName;

1 Answer 1

2

I am not entirely sure what you are trying to achieve by joining on t.Name and u.EmploymentStatusId as they are different data types, varchar and int. When this join is being completed it is trying to implicitly convert t.Name into an int and it contains data that cannot be converted as the error message shows.

If you are trying to get the employment status of the user with CustomerId of 214 I would do something similar to:

 SELECT
 t.Name AS Status

 FROM 
 dbo.Paychecks AS p INNER JOIN dbo.Users AS u ON u.Id=p.UserId 
 INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId
 WHERE u.CustomerId=214 order by u.FirstName;

If I have missed the point please clarify exactly what you are trying to achieve.

5
  • The theory behind this , i wanna know for what reason, employees have terminated
    – RyaN
    Feb 28, 2017 at 11:27
  • My suggested query should provide you with that response.
    – Mayberg
    Feb 28, 2017 at 11:35
  • thank you for this, i have another question ,right now the show all reason for terminated but i just wanna show reason terminated for those who just have EmploymentStatusId = 2 . i know how to do it but i will be 2 where in same line. INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId where u.EmploymentStatusId = 2 WHERE u.CustomerId=214 order by u.FirstName;
    – RyaN
    Feb 28, 2017 at 12:29
  • If you want to add multiple WHERE conditions you need to specify each one followed by an AND constraint as they both need to be true: SELECT t.Name AS Status FROM dbo.Paychecks AS p INNER JOIN dbo.Users AS u ON u.Id=p.UserId INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId WHERE u.CustomerId=214 AND u.EmploymentStatusId = 2 order by u.FirstName;
    – Mayberg
    Feb 28, 2017 at 12:34
  • You do not need the second WHERE, just use the AND
    – Mayberg
    Feb 28, 2017 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.