When you have a higher reputation you get more greeen squares in your header file (I guess it is supposed to signify how competent the responders are) as well I think it might have a reflection on...

By only doing subqueries you basically created a cartesian join. It displayed a record for every possible permutation of the query.

There is a simple explantion of cartesian (cross joins) here.
...

What rownum = 1 is doing is returning only the top row of employee information for each job_id. without the rownum =1 the query will return all matches which is why you get the error message.
...

I know it is possible as I have done similar queries in the past using subqueries. I provided something I put together quickly, not a very good example but it does accomplish what you were inquiring...

I'm not quite sure what you are trying to achieve.

If you just want the job_id then grab it seperately, if you want to see all the employees associated which each job_id you have to return all...

Try something like this.


SELECT DISTINCT emp.Job_ID, a.Employee_ID, a.Last_Name
FROM Employees emp
Join (Select job_id,employee_id,Lastname
From Employees)a on emp.Job_id = a.Job_id...

Mamtha,

If this solved your question can you please mark the thread solved.

thanks.

Yes intersects work well and are much more readable.

Try something like this


Select customer from Table
Where purchasedate between trunc(sysdate,'MM') and LAST_DAY (TO_DATE (trunc(sysdate,'MM')))
and product = 'ABC'
INTERSECT
Select customer...

I think we would need a little more details to what you are asking.

Do you want a list of all customers that purchased a specific product in the current month only IF they had purchased the same...