Select Emp_Number From 
(Select Emp_Number,Amount From TableName Where Exp_No = 1) t 
Inner Join 
TableName t1 On T1.Emp_Number = t.Emp_Number And 
T1.Amount = t.Amount 

Select T1.* From 
(Select Emp_Number,Amount From TableName Where Exp_No = 1) t 
Inner Join 
TableName t1 On T1.Emp_Number = t.Emp_Number And 
T1.Amount = t.Amount

quote:

---

Originally posted by sqllearner

When I use the above query Iam getting the below shown value

E2345 1 Fees 1656
E6622 1 Fees 2356
E9265 1 Fees 5556
E7765 1 Fees 6756

---

Declare @var Table 
(
Emp_Number varchar(10),
exp_no int, 
exp_desc varchar(10),
amount money
)
Insert @var
Select 'E2345',1,'Fees',1656 Union All	
Select 'E2345',2,'Food',456  Union All	
Select 'E2345',3,'Movie',565 Union All	
Select 'E2345',4,'Books',567 Union All	
Select 'E2345',5,'Utility',566 Union All	
Select 'E2345',6,'Gas',340 Union All	
Select 'E2345',7,'Elec',1656 Union All	
Select 'E2345',8,'CDs',133 Union All	
Select 'E2345',9,'Rent',157 Union All	
Select 'E6622',1,'Fees',2356 Union All	
Select 'E6622',2,'Food',456 Union All
Select 'E6622',3,'Movie',565 Union All
Select 'E6622',4,'Books',2356 Union All
Select 'E6622',5,'Utility',566 Union All
Select 'E6622',6,'Gas',340 Union All
Select 'E6622',7,'Elec',166 Union All
Select 'E6622',8,'CDs',133 Union All	
Select 'E6622',9,'Rent',157 Union All
Select 'E9265',1,'Fees',5556 Union All
Select 'E9265',2,'Food',456 Union All
Select 'E9265',3,'Movie',565 Union All
Select 'E9265',4,'Books',567 Union All
Select 'E9265',5,'Utility',556 Union All
Select 'E9265',6,'Gas',340 Union All
Select 'E9265',7,'Elec',656 Union All
Select 'E9265',8,'CDs',133 Union All
Select 'E9265',9,'Rent',157 Union All
Select 'E7765',1,'Fees',6756 Union All
Select 'E7765',2,'Food',456 Union All
Select 'E7765',3,'Movie',565 Union All
Select 'E7765',4,'Books',567 Union All
Select 'E7765',5,'Utility',566 Union All
Select 'E7765',6,'Gas',340 Union All
Select 'E7765',7,'Elec',156 Union All
Select 'E7765',8,'CDs',6756 Union All
Select 'E7765',9,'Rent',157 


Select t3.* From 
  (Select t1.Emp_Number,t1.Amount From 
	(Select Emp_Number,Amount From @var Where Exp_No = 1) t 
	Inner Join 
	(Select * From @var ) t1 On
	 T1.Emp_Number = t.Emp_Number And 
	 T1.Amount = t.Amount
	 Group by t1.Emp_Number ,t1.Amount
	 Having Count(1) >1) 
   as t2
   Inner Join 
   @var t3 on 
   t2.Amount = t3.Amount and t2.emp_number = t3.emp_number