[wartech]

Hello,
I am getting the error message "sql command not properly ended" when I try to run the following query. I am using Oracle 10g XE. I do not understand what I am missing in this statement. Your help is appreciated.

select e.last_name,e.department_id,j.job_id,j.job_title,MIN(e.salary),MAX(e.salary)
from employees e, jobs j
on e.job_id = j.job_id
group by e.department_id
having e.department_id in (50,60)
order by e.job_id,e.last_name;

[June7]

I don't know oracle but do use SQL and your string looked good to me so searched and found example from this site, it uses JOIN keyword, maybe makes a difference:
http://www.sqlinfo.n...acle_SELECT.php

SELECT  
	   b.COURSE_NAME		,
	   a.CLASS_END_DATE	 , 
	   a.CLASS_START_DATE						 
 
FROM		 CLASSES a 
		JOIN COURSES b 
			 ON  b.COURSE_DESIGNATER = a.COURSE_DESIGNATER_FK

[wartech]

Hi June7,
When I used the join I received the error ORA-00979: not a GROUP BY expression

The only way I can get this code to work somewhat is if I use this code:

select e.last_name,e.department_id,j.job_id,j.job_title
from employees e join jobs j
on e.job_id = j.job_id
where e.department_id in (50,60)
order by e.job_id,e.last_name;

My problem seems to be when I am using MAX and MIN

[baavgai]

Let's look at one table first:

select e.job_id, e.last_name, e.department_id, MIN(e.salary), MAX(e.salary)
	from employees e
	where e.department_id in (50,60)
	group by e.job_id, e.last_name, e.department_id

That should give you the first bit of data you say you're lookng for. Now for the jobs table contribution.

select e.last_name,
		e.department_id,
		j.job_id,
		j.job_title,
		e.salary_min, e.salary_max
	from (
		select e.job_id, e.last_name, e.department_id, 
				MIN(e.salary) as salary_min, MAX(e.salary) as salary_max
			from employees e
			where e.department_id in (50,60)
			group by e.job_id, e.last_name, e.department_id
	) e
		inner join jobs j
			on e.job_id = j.job_id
	order by e.job_id,e.last_name;

That should work. You can get away without the sub query, by using a larger group by. Howevr, the above version is probably more efficient.

[wartech]

Thanks Baagvi!! It works and I understand where I went wrong.