Skip to content

Instantly share code, notes, and snippets.

@frioux
Created Jul 31, 2012
Embed
What would you like to do?
<3 correlated subqueries
SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], [key_server_side_mia], [button_server_side_mia], [messenger_server_side_
mia], [key_pro_server_side_mia], [key_status], [button_status], [messenger_status], [key_pro_status], [is_group_active], [last_checkin], [macro]
FROM (
SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], [key_server_side_mia], [button_server_side_mia], [messenger_server_s
ide_mia], [key_pro_server_side_mia], [key_status], [button_status], [messenger_status], [key_pro_status], [is_group_active], [last_checkin], [macro], ROW_NUMBER() OVE
R (
ORDER BY
SELECT( 1 )
) AS [rno__row__index]
FROM (
SELECT [me].[id], [me].[name], [me].[group_id], [me].[user], [me].[version], [me].[is_active], (
SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE( ) ) THEN 1 ELSE 0 END
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
LEFT JOIN [Computer_Device] [computer_device_links]
ON [computer_device_links].[device_id] = [device].[id]
LEFT JOIN [Computers] [computer]
ON [computer].[id] = [computer_device_links].[computer_id]
LEFT JOIN [ComputerGroups] [group]
ON [group].[id] = [computer].[group_id]
WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '0' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[
id]
) AS [key_server_side_mia], (
SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE( ) ) THEN 1 ELSE 0 END
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
WHERE [device].[type_id] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [button_server_side_mia], (
SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE( ) ) THEN 1 ELSE 0 END
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
WHERE [device].[type_id] = '3' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [messenger_server_side_mia], (
SELECT CASE WHEN device.last_checkin < DATEADD ( second, 4 * 60, GETUTCDATE( ) ) THEN 1 ELSE 0 END
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
LEFT JOIN [Computer_Device] [computer_device_links]
ON [computer_device_links].[device_id] = [device].[id]
LEFT JOIN [Computers] [computer]
ON [computer].[id] = [computer_device_links].[computer_id]
LEFT JOIN [ComputerGroups] [group]
ON [group].[id] = [computer].[group_id]
WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[
id]
) AS [key_pro_server_side_mia], (
SELECT [device].[status_id]
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
LEFT JOIN [Computer_Device] [computer_device_links]
ON [computer_device_links].[device_id] = [device].[id]
LEFT JOIN [Computers] [computer]
ON [computer].[id] = [computer_device_links].[computer_id]
LEFT JOIN [ComputerGroups] [group]
ON [group].[id] = [computer].[group_id]
WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '0' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [key_status], (
SELECT [device].[status_id]
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
WHERE [device].[type_id] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [button_status], (
SELECT [device].[status_id]
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
WHERE [device].[type_id] = '3' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [messenger_status], (
SELECT [device].[status_id]
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
LEFT JOIN [Computer_Device] [computer_device_links]
ON [computer_device_links].[device_id] = [device].[id]
LEFT JOIN [Computers] [computer]
ON [computer].[id] = [computer_device_links].[computer_id]
LEFT JOIN [ComputerGroups] [group]
ON [group].[id] = [computer].[group_id]
WHERE [device].[type_id] = '2' AND [group].[is_lynx_key_pro] = '1' AND [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [key_pro_status], (
SELECT [group_alias].[is_active]
FROM [ComputerGroups] [group_alias]
WHERE [group_alias].[id] = [me].[group_id]
) AS [is_group_active], (
SELECT MAX( [device].[last_checkin] )
FROM [Computer_Device] [computer_device_links_alias]
JOIN [Devices] [device]
ON [device].[id] = [computer_device_links_alias].[device_id]
WHERE [device].[is_enabled] = '1' AND [computer_device_links_alias].[computer_id] = [me].[id]
) AS [last_checkin], [macro].[to] AS [macro]
FROM [Computers] [me]
LEFT JOIN [Lookup] [macro]
ON [macro].[from] = [me].[name]
JOIN [ComputerGroups] [group]
ON [group].[id] = [me].[group_id]
WHERE [group].[is_active] = '1' AND [me].[is_active] = '1'
) [me]
) [me]
WHERE [rno__row__index] >= '1' AND [rno__row__index] <= '25'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment