Gabriel Leong Gabriel Leong - 1 year ago 69
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 Source

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;