Click here to Skip to main content
Click here to Skip to main content

Tagged as

Selecting parent records with specific subset of child records

, 15 Nov 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
I will show you how you can select one parent row with two latest child rows using T-SQL
Introduction

Imagine this kind of SQL query - you have a parent/child tables such as Product/ProductItem, Company/Employee, Article/Categories where parent and child tables are connected with one-to-many relationship. Let we assume that you have following Company/Employee tables with the following structure:

Company
+CompanyId
+CompanyName
 
Employee
+EmployeeId
+CompanyId
+EmployeeName
+DateHired
+Salary

Each company can have several employees which are connected via CompanyId foreign key. Also, date when employee is hired is placed in the Employee table

Problem

How can you create SQL query that returns parent row and exactly two child rows? examples of such kind of queries are:
1. List all companies and employee who has been hired first
2. List all companies and last two hired employees
3. List all companies with two employees with the highest salaries
 
Example of result for the query number 2 would be:

Companies | Date Hired | Employee
-----------------------------------------
Company1 | 17/11/2011 | John Spenser
Company1 | 16/11/2011 | Mike Johnson
Company2 | 10/11/2011 | Jasmin Fleet
Company2 | 05/11/2011 | George Lee
Company3 | 14/11/2011 | John Do
Company3 | 13/11/2011 | Adam Johnson
Company4 | 06/11/2011 | Adelle Spenser
Company4 | 28/10/2011 | Stephen Murphy
 
Solution
 
You will need to join Company and Employee tables and partition the results by CompanyId, in order to group results by companies.
Then, you will need order employees by date hired (or salary - this is specific for your problem) and find order index with this condition.
In the final query you will filter results by order index (e.g. if you need last two employees you will put order_index < 2 in the WHERE clause)
 
In the following listing is shown that kind of code:
 
WITH company_employees (CompanyName, EmployeeName, DateHired, order_index) AS (
	
SELECT	Company.CompanyName,
	Employee.EmployeeName, 
	Employee.DateHired, 
	ROW_NUMBER() OVER( PARTITION BY Company.CompanyId
				ORDER BY Employee.DateHired desc)
	FROM         Company
	INNER JOIN   Employee
		ON Employee.CompanyId = Company.CompanyId
)
SELECT * FROM company_employees
WHERE order_index IN ( 1,2)
 
In this query I have user common table expression (neater replacement for sub-query) where I have joined parent and child table.
ROW_NUMBER function returns me index of the employee partitioned by companies ordered in the by the date hired i descending order)
In the main query I have filtered only those records that have order_index 1 or 2 because I need to find only last two hired employees in each company.
 
If I have placed ascending order in the ROW_NUMBER, two employees that are hired first would be returned. instead of the DateHired I could use any other field such as salary.
 
With minor change you can also use this query if you have similar requirement.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jovan Popovic
Architect Gowi
Serbia Serbia
Started as a young scientist - winning the highest national awards in mathematics, physics, electrotechnics, and electronics.
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia, as a first in the class, as a Master of Software Sciences.
Currently working in Gowi as a Software engineer, architect, and project manager since 2004 - mostly using Microsoft technologies (ASP.NET, C#). Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 15 Nov 2011
Article Copyright 2011 by Jovan Popovic
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid