Nested Join

Sample Data

DECLARE @TableA TABLE([First] INT)INSERT INTO @TableA VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9) DECLARE @TableB TABLE([Second] INT)INSERT INTO @TableB VALUES (2),(3),(4),(8) DECLARE @TableC TABLE([Third] INT)INSERT INTO @TableC VALUES (3),(4),(8)

Query

SELECT * FROM   @TableA TableA        LEFT JOIN @TableB TableB        JOIN @TableC TableC        ON TableB.[Second] = TableC.[Third]        AND TableC.[Third] < 5        ON TableA.[First] = TableB.[Second]

Also can written as

SELECT * FROM   @TableA TableA        LEFT JOIN     (SELECT       *                       FROM   @TableB TableB                              JOIN @TableC TableC                                   ON TableB.[Second] = TableC.[Third]                       WHERE  TableC.[Third] < 5)[SecondThird]        ON TableA.[First] = [SecondThird].[Second]

Desired output