Help Simplifying SQL query

From: Montse <nospam_at_mysticdragon.com>
Date: 19 Mar 2003 09:23:05 -0800
Message-ID: <a4beba43.0303190923.61192936@posting.google.com>

Hi there,

I'm new to SQL, only been working with a few months. I've created this query that does exactly what I need. However, it is very apparent that I can simplify it as I am reusing the same select statement code 3 times.

It seems I should be able place this select statement in the from statement once and then use an alias or something to access it earlier in the statement.

Any ideas on how to do this without using views?

Thanks in advance!

select emp_lname, emp_fname, dept_name, salary,

	salary -


	(select avg(salary) from sales2.employee e2

	where dept_id=e.dept_id
	) as Difference,


	(select avg(salary) from sales2.employee e2

	where dept_id=e.dept_id
	) as AVERAGE

from sales2.employee e, sales2.department d where d.dept_id = e.dept_id
and

	salary >


	(select avg(salary) from sales2.employee e2

	where dept_id=e.dept_id
	group by dept_id)

order by dept_name
/ Received on Wed Mar 19 2003 - 11:23:05 CST