Skip navigation links

MySQL Forums :: MySQL Query Browser :: Query Fails - Need Help Figuring it out


Advanced Search

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

Options: ReplyQuote


Subject Written By Posted
Query Fails - Need Help Figuring it out C R 02/10/2012 06:38AM
Re: Query Fails - Need Help Figuring it out Scott Nemes 02/10/2012 10:51AM
Re: Query Fails - Need Help Figuring it out C R 02/12/2012 03:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.