DEV Community

Chris Cooper
Chris Cooper

Posted on • Originally published at coopsblog.wales on

Select query using an outer apply

This is a simple example of using an SQL Outer apply to create a select query that can create queryable custom sub fields.

Before being shown outer apply logic, I was using a temp tables and sub query. The sub table added the data required on the temp table and then I queried the temp table as needed. Now I can do it all in one simpler and quicker select query.

The simple query

This example is done on a user_extended table that is a Key and value pair logic, that acts as a dumping group for records that maybe used later. When a record is inserted and it contains the same key as a record in the DB table, the DB table record is disabled and this new record is made active.

The purpose of this it to check if the created date record is the active and is not disabled.

SELECT Parent.Username, ActiveDate,DisableDate FROM User AS Parent OUTER APPLY ( SELECT TOP 1 MAX (CASE WHEN Child.Active= 1 AND Child.key = 'LastLoggedIn' THEN Child.DateOfCreation END) AS ActiveDate, MAX (CASE WHEN Child.Active = 0 AND Child.key = 'LastLoggedIn' THEN Child.DateOfCreation END) AS DisableDate FROM [Users_Extended] AS Child WHERE Child.UserId = Parent.Id group by DateOfCreation ORDER BY Child.DateOfCreation DESC ) Child WHERE Parent.key = 'LastLoggedIn' AND Parent.Active = 0 AND ActiveDate < DisableDate; ORDER BY Parent.Id DESC

Top comments (0)