CODEWITHSUNDEEP
phpmysqlsql
user1178070
user1178070

Reputation:

Sql Joining two tables and fetching a value

I have two tables 'talent_empcomp' and 'talent_employee_details_v' where i need to fetch a value from 'Emp_Compensation' which is present only in 'talent_empcomp' table but 'Emp_Id' is common in both tables and has same values..i need to fetch 'Emp_Compensation' for a perticular 'Emp_Id' below is the joint query iam running ..but iam getting an error message "Column 'Emp_Id' in where clause is ambiguous"

SELECT A.*, B.Emp_Compensation 
FROM talent_employee_details_v A, talent_empcomp B   
WHERE Emp_FirstName like '%' and Emp_Id='$Emp_Id' ORDER BY Emp_FirstName

Upvotes: 0

Views: 127

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

First, you should use explicit join syntax. Second you should use the table aliases you define:

SELECT A.*, B.Emp_Compensation
FROM talent_employee_details_v A join
     talent_empcomp B
     on a.EmpId = B.Emp_id
WHERE A.Emp_FirstName like '%' and A.Emp_Id='$Emp_Id'
ORDER BY A.Emp_FirstName

It is a good idea to put aliases before column references so you know where the values are coming from. I am guessing the names are from "A".

Upvotes: 1

the_red_baron
the_red_baron

Reputation: 888

This error occurs when you run a join and have not specified which table to select the column from. All it takes is to specify the table before the column name, like A.Emp_id

Upvotes: 0

Vlad Balmos
Vlad Balmos

Reputation: 3412

SELECT A.*, B.Emp_Compensation 
FROM talent_employee_details_v A, talent_empcomp B   
WHERE Emp_FirstName like '%' and B.Emp_Id='$Emp_Id' ORDER BY Emp_FirstName

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Well, you're trying to filter of the Emp_Id column, but it is present in both tables, so the SQL interpreter does not know which column to filter. You need to explicitly qualify the column like B.Emp_Id.

SELECT A.*, B.Emp_Compensation 
FROM talent_employee_details_v A, talent_empcomp B   
WHERE Emp_FirstName like '%' and B.Emp_Id='$Emp_Id' ORDER BY Emp_FirstName

By the way: Your query might run faster if you remove the like % condition.

Upvotes: 0

Related Questions