Consider the following scenario: You need to produce a query which joins several tables but from one of the tables you only need the last matching record. This can be achieved by using the T-SQL OUTER APPLY operator. Consider the following simple database structure:
CREATE TABLE Clients ( client_id int NOT NULL PRIMARY KEY ,client_name varchar(30) ,client_address varchar(40) ) CREATE TABLE Operators ( operator_id NOT NULL PRIMARY KEY ,operator_name varchar(30) ) CREATE TABLE Support ( support_id int NOT NULL PRIMARY KEY ,client_id int NOT NULL ,assigned_operator_id NOT NULL ,support_description varchar(30) ,support_detail text ,support_open_date datetime ,support_close_date datetime ) CREATE TABLE Notes ( note_id int NOT NULL PRIMARY KEY ,support_id NOT NULL ,entering_operator_id NOT NULL ,note_date_time datetime NOT NULL ,note_detail text )
Let’s say we want to create a query which will list the support ticket id,support ticket description,assigned operator name,client name,support ticket open date,and the last note entered against the support ticket for all open support tickets. The following query using outer apply produces the desired results:
SELECT support_id, support_desc, operator_name, client_name, support_open_date, note_date_time, note_detail FROM Support INNER JOIN operator ON assigned_operator_id=operator_id INNER JOIN client ON Support.client_id=client.client_id OUTER APPLY (SELECT top 1 * FROM Notes WHERE Notes.support_id = Support.support_id ORDER BY note_date_time desc) Notes WHERE support_close_date IS NULL
The outer apply statement will select the top (read last because we are ordering by descending date) support note if any exists. The support ticket will still be returned even if a support note doesn’t exist.