Gabriel Leong Gabriel Leong - 13 days ago 5
SQL Question

Show data from two tables which have same column name

I have two table in Access, Employee and Dept.

In Employee table there is empname column and deptcode column, while in Dept table there is deptcode column and deptname column.

I want to do a query which shows empname, deptcode and deptname in a new table. I have tried:

SELECT empname, deptcode, deptname
FROM employee,dept


And it cannot work as the deptcode exist in both table and it creates error. Can anyone kindly tell me how to solve this error problem?

Answer

You need to alias your tables in the FROM clause and then use the table alias in the SELECT statement.

select e.empname,d.deptcode,d.deptname 
from employee e
inner join dept d
  on e.deptcode = d.deptcode;