sql query for select based on some count

lejo

In My employee table i have NoOfeligiblechildSchoolFee field ,its number is different for each employee. i want to select child details from another table(Dependent Table) based on NoOfeligiblechildSchoolFee field

Employee Table

Emp_ID,EmpName,NoOfeligiblechildSchoolFee etc

Data

1 3 Alan

1 2 Bibin

1 2 Cibin

(Dependent Table)

Dependent_ID,Emp_ID,ChidName etc

1 1 alan1

2 1 alan2

3 1 alan3

4 1 alan4

5 2 Bibin1

6 2 Bibin2

7 2 Bibin3

8 3 Cibin1

9 3 Cibin2

9 3 Cibin3

for the first employee 3 kid,sec employee 2 kid,third employee 2

Fetch Dependent data for employee's based on NoOfeligiblechildSchoolFee number

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Aravind Govindaraj3y ago
Accepted answer
I assume you need to use the SQL rank concept and the condition suppose to be rank <= NoOfeligiblechildSchoolFee. let me know if you need any more info on the same.

Sachin Singh3y ago

Here is the solution

Test data for your reference:-

IF Object_ID('tempdb..#Employee') is not null drop table #Employee
Create table #Employee
(
  EmpID INT Identity primary key,
  Name varchar(50),
  NoOfEligibleChild INT

)

IF Object_ID('tempdb..#Dependent') is not null drop table #Dependent
Create table #Dependent
(
  DependID INT Identity primary key,
  EmpID INT,
  ChildName varchar(50)

)

Insert into #Employee(Name,NoOfEligibleChild) values('Sachin',3)

Insert into #Employee(Name,NoOfEligibleChild) values('Arjun',2)

Insert into #Dependent(EmpID,ChildName) values(1,'c1')
Insert into #Dependent(EmpID,ChildName) values(1,'c2')
Insert into #Dependent(EmpID,ChildName) values(1,'c3')
Insert into #Dependent(EmpID,ChildName) values(1,'c4')

Insert into #Dependent(EmpID,ChildName) values(2,'a1')
Insert into #Dependent(EmpID,ChildName) values(2,'a2')
Insert into #Dependent(EmpID,ChildName) values(2,'a3')

  select EmpID,Name as empName, ChildName from (select e.EmpID,e.Name,d.ChildName,e.NoOfEligibleChild, ROW_NUMBER() over(partition by e.EmpID order by DependID)RN from #Employee e
  JOIN #Dependent d
  on e.EmpID=d.EmpID)t
  where RN<=t.NoOfEligibleChild