Highest # of records from inner join

Question

Hello experts,

I am inner joining 3 tables A, B and C. Let's say table A has Department field. I need to get the top 3 departments with highest records from inner join. Can someone please help.

Thanks,

AV

Answer 1

This kind of limited results can be retrieved by using correlated sub-queries, e.g.

SELECT  *
FROM    SalesLT.SalesOrderDetail SOD
WHERE   ProductID IN ( SELECT TOP 3
                                ProductID
                       FROM     SalesLT.SalesOrderDetail SOD
                       ORDER BY OrderQty DESC ) ;

But a concise and complete example would be nice..

Answer 2

You can use CROSS APPLY

SELECT B.*, C.*, A.Departments
FROM TableB B
	JOIN TableC C 
		ON B.ID = C.BID
CROSS APPLY (SELECT TOP 3 Departments FROM TableA WHERE ID = B.BID) A

---

Abdallah El-Chal, PMP

Answer 3

Hi Stefan,

Thanks for promt response. Let me give you my scenario.

Table A has DEPT_ID, DEPT_NAME

Table B has DEPT_ID, DIV_ID, DIV_NAME

Table C has DIV_ID, EMP_ID, EMP_LAST, EMP_FIRST

A department has several divisions and divisions contain employees. I am trying to count the number of employees in each department and get top 3 departments with highest number of employees by joining 3 tables. Can you please frame a query around my requirement please.

 

Answer 4

Hi Abdallah,

Thanks for promt response. Let me give you my scenario.

Table A has DEPT_ID, DEPT_NAME

Table B has DEPT_ID, DIV_ID, DIV_NAME

Table C has DIV_ID, EMP_ID, EMP_LAST, EMP_FIRST

A department has several divisions and divisions contain employees. I am trying to count the number of employees in each department and get top 3 departments with highest number of employees by joining 3 tables. Can you please frame a query around my requirement please.

Answer 5

Here is a sample based on AdventureWorks database.

;with cte as (select D.*, E.Gender, E.HireDate, E.VacationHours,
sum(E.VacationHours) over (partition by D.DepartmentID) as VacHoursTotal
from HumanResources.Department D
INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
ON D.DepartmentID = EDH.DepartmentID
INNER JOIN HumanResources.Employee E ON EDH.BusinessEntityID = E.BusinessEntityID),

cte2 as (select top (3) * from (select distinct DepartmentID, VacHoursTotal from cte) X 
order by VacHoursTotal DESC)

select cte.* from cte inner join cte2 on cte.DepartmentID = cte2.DepartmentID
ORDER BY cte2.VacHoursTotal DESC

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

The sample I just posted should help you.

Here it is for your tables structures:

;with cte as (select D.*, Div.Div_Name, E.Emp_ID, E.Emp_Last, E.Emp_First, X.cntEmployees

from Departments D inner join Divisions D on D.Dept_ID = Div.Dept_ID inner join Employees E on Div.Div_ID = E.Div_ID

CROSS APPLY (select count(distinct E1.EmpID) as cntEmployees from Employees E1 inner join Divisions Div1 on E1.Div_ID = Div1.Div_ID

where Div1.Dept_ID = D.Dept_ID) X),

cte2 as (select top (3) * from (select distinct Dept_ID, cntEmployees from cte) X order by cntEmployees DESC)

select cte.* from cte inner join cte2 on cte.Dept_ID = cte2.Dept_Id

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Hello Naomi,

Thanks for the response. I am looking at your sample. I am new to SQL. Can you please explain me the sample as to what it is doing. Would really help me in my testing.

AV

Answer 8

I use the inner join and by using CROSS APPLY I can get the distinct count of Employees per department (cross apply is like a correlated subquery). I could have used just COUNT(*) OVER but I thought we need a distinct count (although it is probably distinct anyway).

Then I get 3 top counts from distinct set of department/count and then join back to the first cte to get all extra info back.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

Hi,

I agree with Naomi's answer.

Logic is same as I thought to implement.

Answer 10

If you can provide sample data. It will be better.

At least provide your table name, it will help to give answer which is more understandable.

Answer 11

Hi did. Here is his response:

Hi Stefan,

Thanks for promt response. Let me give you my scenario.

Table A has DEPT_ID, DEPT_NAME

Table B has DEPT_ID, DIV_ID, DIV_NAME

Table C has DIV_ID, EMP_ID, EMP_LAST, EMP_FIRST

A department has several divisions and divisions contain employees. I am trying to count the number of employees in each department and get top 3 departments with highest number of employees by joining 3 tables. Can you please frame a query around my requirement please.

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog