Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query
Sql Query [message #359730] Tue, 18 November 2008 01:49 Go to next message
rajput.anshu
Messages: 45
Registered: October 2008
Location: Bangalore
Member
I want to know qurey of department with no employees?
I know one that:

select * from employees where rownum<=&n
minus
select * From employees where rownum<=&n;

I want to know other examples of this query?
Re: Sql Query [message #359734 is a reply to message #359730] Tue, 18 November 2008 01:58 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
would you not need to first count the number of employees in the department?
Re: Sql Query [message #359736 is a reply to message #359734] Tue, 18 November 2008 02:03 Go to previous messageGo to next message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
No,I don't want to count. I want to only display deparments which have no employees. I given there one example.
Re: Sql Query [message #359741 is a reply to message #359736] Tue, 18 November 2008 02:07 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
This might get you going:
select count(*),deptno from scott.emp group by deptno;

How will you know which department has no employees without first know how many employees there are in each department
Re: Sql Query [message #359746 is a reply to message #359741] Tue, 18 November 2008 02:14 Go to previous messageGo to next message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Friend you didn't get my question.
Suppose in employees....deptno is 10,20,30,40
and in departments......deptno is 10,20,30,40,50,60,70
now I want to display 50,60,70...
I already given there one example just see that one..
Re: Sql Query [message #359752 is a reply to message #359730] Tue, 18 November 2008 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select * from employees where rownum<=&n
minus
select * From employees where rownum<=&n;

What is this supposed to do?
You remove from the n first lines you found in first query the n first lines you found in the same query.

You have no departement table you can't know from employees table which departements have no employee.

Regards
Michel
Re: Sql Query [message #359753 is a reply to message #359746] Tue, 18 November 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK now yo have a departements table.
Then think about your first query and correct it to get the result (this is one way among several).

Regards
Michel
Re: Sql Query [message #359755 is a reply to message #359746] Tue, 18 November 2008 02:29 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
I'm not really following you.
You want to see the departments with no employees.
How are you going to know if a department has no employees if you are not checking the department table for all the departments?

This is how I would have tried it:
SELECT d.dname, COUNT(e.deptno)
FROM   scott.emp e, scott.dept d
WHERE  e.deptno(+) = d.deptno
GROUP  BY d.dname
having COUNT(e.deptno) = 0;


This brings me all the rows where the departments that have no employees. As for your query you don't get any data becasue you are simply subtracting the exact same info from each other. Also not sure how checking rownum can help determine if there are employees in the department?
Re: Sql Query [message #359758 is a reply to message #359746] Tue, 18 November 2008 02:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rajput.anshu and das.liton share IP addresses; are there two of you, or ... what? It would be better if each of you uses his/her own login credentials while on OraFAQ Forum, in order to avoid confusion.

Another way could be use of the NOT IN operator; check the documentation to see how to use it. Which documentation? Links are available in OraFAQ Forum Guide.
Re: Sql Query [message #359773 is a reply to message #359758] Tue, 18 November 2008 03:00 Go to previous message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Thanks Tarmenel. That's what i am looking for.
Previous Topic: how to impose the check constraint
Next Topic: Case Expression
Goto Forum:
  


Current Time: Sun Dec 11 02:33:02 CST 2016

Total time taken to generate the page: 0.11036 seconds