can anybody help to writting this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

can anybody help to writting this query

Author

Topic

noorahmed.shaik
Starting Member

11 Posts

Posted - 2007-06-25 : 09:36:28

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 * 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

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
6   prabha   D40 20000.75

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

Noor Ahmed
Keysoft solution

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 10:39:18

[code]SELECT e.*
FROM employee e
INNER JOIN
(
SELECT salary
FROM employee
GROUP BY salary
HAVING COUNT(*) > 1
) d
ON e.salary = d.salary[/code]

KH
[spoiler]Time is always against us[/spoiler]

noorahmed.shaik
Starting Member

11 Posts

Posted - 2007-06-25 : 10:46:45

No, again its giving the same wrong output..........

Noor Ahmed
Keysoft solution

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 10:59:06

There are 3 records with salary 20000.75
How do you decide which one to not pick? And which two to pick?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-25 : 10:59:57

sorry did not noticed the "same dept" condition

SELECT	e.*
FROM   	employee e
INNER JOIN
(
	SELECT	dno, salary
	FROM	employee
	GROUP BY dno,salary
	HAVING COUNT(*) > 1
) d
ON  	e.dno	 = d.dno
AND	e.salary = d.salary

KH
[spoiler]Time is always against us[/spoiler]

noorahmed.shaik
Starting Member

11 Posts

Posted - 2007-06-25 : 11:04:08

i want the records, in which salary is same and department is also same.

Noor Ahmed
Keysoft solution

noorahmed.shaik
Starting Member

11 Posts

Posted - 2007-06-25 : 11:06:22

THANK YOU VERY MUCH KHTAN.......

Noor Ahmed
Keysoft solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources