Home » SQL & PL/SQL » SQL & PL/SQL » Anybody can help for this small query
Anybody can help for this small query [message #247291] Mon, 25 June 2007 07:15 Go to next message
noorahmed.shaik
Messages: 6
Registered: June 2007
Location: Bangalore
Junior Member

I have table name employee with attributes like eno,ename dno,salary. My problem is that i need select complete information of the emloyee's when employee getting same salary in same depatment.

Eg: I have the table like this
eno ename dno salary
1 Noor D10 10000.50
2 Ahmed D20 20000.75
3 jaya D30 15000.65
4 hema D10 10000.50
5 rupa D20 20000.75
6 prabha D40 20000.75
7 lalitha D40 12000.75
8 vetri D40 12000.75

and i need the output as fallows
eno ename dno salary
1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75

so I written the query like this

SELECT eno,ename,dno,salary FROM employee
where dno in(SELECT dno FROM employee
group by dno,salary
having count(dno)>1) and
salary in (SELECT salary FROM sub
group by dno,salary
having count(salary)>1)

but i got the output as fallows

1 Noor D10 10000.50
4 hema D10 10000.50
7 lalitha D40 12000.75
8 vetri D40 12000.75
2 Ahmed D20 20000.75
5 rupa D20 20000.75
6 prabha D40 20000.75

so plz somebody help me to get the output what i need........


[Updated on: Mon, 25 June 2007 07:25]

Report message to a moderator

Re: Anybody can help for this small query [message #247292 is a reply to message #247291] Mon, 25 June 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Then this may help you:
SELECT eno,ename,dno,salary FROM employee
where (dno,salary) in 
      (SELECT dno,salary FROM employee
       group by dno,salary
       having count(*)>1)
/

Regards
Michel
Re: Anybody can help for this small query [message #247294 is a reply to message #247291] Mon, 25 June 2007 07:24 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Read and follow How to format your posts.

Reds
Thangam


Re: Anybody can help for this small query [message #247298 is a reply to message #247294] Mon, 25 June 2007 07:38 Go to previous messageGo to next message
noorahmed.shaik
Messages: 6
Registered: June 2007
Location: Bangalore
Junior Member

Thank you michel


can u help me to write this same query in sql server 2005

Rgds
Noor
Re: Anybody can help for this small query [message #247299 is a reply to message #247291] Mon, 25 June 2007 07:40 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You might want to ask that on a SQL Server forum
Previous Topic: No wait ..
Next Topic: Recently updated records.
Goto Forum:
  


Current Time: Sun Dec 11 04:23:54 CST 2016

Total time taken to generate the page: 0.21978 seconds