Query Fails - Need Help Figuring it out
Posted by:
C R
Date: February 10, 2012 06:38AM
I need to create a view called EmployeeBoss containing employee number, employee name, employee salary and boss name from a table called emp. I need to use a self join. I've been trying the queries below, but somehow there's something missing that I can figure it out. Can anyone please take a look and give me some feedback. I'm new to building SQL queries.
This is the table I'm trying to extract the information described above using a self join:
mysql> desc emp;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| empno | smallint(6) | NO | PRI | 0 | |
| empname | varchar(25) | YES | | NULL | |
| empsalary | decimal(8,2) | YES | | NULL | |
| deptname | varchar(30) | YES | | NULL | |
| bossno | smallint(6) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
I've written these two queries and neither of them work, they both throw an error:
Query 1 attempt
CREATE VIEW EmployeeBoss ( employee_number, employee_name,
employee_salary, boss_name) AS SELECT T1.empno, T1.empname, T1.empsalary, T2.empname FROM emp T1, emp T2 WHERE T1.bossno = T2.empno;
error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp T1, emp T2 WHERE T1.b
ossno = T2.empno' at line 2
Query 2 attempt:
CREATE VIEW EmployeeBoss ( employee_number, employee_name,
employee_salary, boss_name) AS SELECT T1.empno, T1.empname, T1.empsalary, T2.empname FROM emp T1 JOIN emp T2 ON T1.bossno = T2.empno;
Error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'emp T1 JOIN emp T2 ON T1.
bossno = T2.empno' at line 2