SELECT [t].[Nickname], [t].[SquadId], [t0].[Name], [t0].[IsAutomatic], [t0].[Id], [t1].[Nickname], [t1].[Rank], [t1].[SquadId]
FROM (
    SELECT [o].[Nickname], [o].[SquadId], [o].[FullName]
    FROM [Officers] AS [o]
) AS [t]
LEFT JOIN (
    SELECT [w].[Name], [w].[IsAutomatic], [w].[Id], [w].[OwnerFullName]
    FROM [Weapons] AS [w]
    WHERE [w].[IsAutomatic] = CAST(1 AS bit)
) AS [t0] ON [t].[FullName] = [t0].[OwnerFullName]
LEFT JOIN (
-- HERE
    SELECT [t2].[Nickname], [t2].[Rank], [t2].[SquadId], [t2].[FullName], [t2].[LeaderNickname], [t2].[LeaderSquadId]
    FROM (
        SELECT [g0].[Nickname], [g0].[SquadId], [g0].[FullName], [g0].[LeaderNickname], [g0].[LeaderSquadId], [g0].[Rank]
        FROM [Gears] AS [g0]
        UNION ALL
        SELECT [o0].[Nickname], [o0].[SquadId], [o0].[FullName], [o0].[LeaderNickname], [o0].[LeaderSquadId], [o0].[Rank]
        FROM [Officers] AS [o0]
    ) AS [t2]
-- HERE
) AS [t1] ON [t].[Nickname] = [t1].[LeaderNickname] AND [t].[SquadId] = [t1].[LeaderSquadId]
ORDER BY [t].[FullName], [t].[Nickname], [t].[SquadId], [t0].[Id], [t1].[FullName], [t1].[Nickname]