-----------------------------------------
-- START CREATE TABLES

CREATE TABLE #tmp_nested_user
(userid INT, username VARCHAR(20))

INSERT INTO #tmp_nested_user
SELECT 1, 'Mark'
UNION ALL
SELECT 2, 'Contraflo'
UNION ALL
SELECT 3, 'You'

CREATE TABLE #tmp_nested_address
(userid INT, home_address VARCHAR(20))

INSERT INTO #tmp_nested_address
SELECT 1, 'A street'
UNION ALL
SELECT 3, 'Another street'

CREATE TABLE #tmp_nested_counta
(userid INT, cta INT)

INSERT INTO #tmp_nested_counta
SELECT 1, 20
UNION ALL
SELECT 2, 12

CREATE TABLE #tmp_nested_countb
(userid INT, ctb INT)

INSERT INTO #tmp_nested_countb
SELECT 1, 10
UNION ALL
SELECT 3, 6

-- END CREATE TABLES
-----------------------------------------


-----------------------------------------
-- START EXAMPLES

-- ORIGINAL
SELECT
	tb3.userid,
	tb3.username,
	tb4.userid,
	tb4.home_address,
	tb1.userid,
	tb1.cta,
	tb2.userid,
	tb2.ctb
FROM	
	#tmp_nested_counta AS tb1
	LEFT JOIN #tmp_nested_countb AS tb2
		ON tb1.userid = tb2.userid 
	RIGHT JOIN #tmp_nested_user AS tb3
		LEFT JOIN #tmp_nested_address AS tb4
			ON tb3.userid = tb4.userid 
		ON tb1.userid = tb3.userid

-- STEP 1 - Parenthesis
SELECT
	tb3.userid,
	tb3.username,
	tb4.userid,
	tb4.home_address,
	tb1.userid,
	tb1.cta,
	tb2.userid,
	tb2.ctb
FROM	
	#tmp_nested_counta AS tb1
	LEFT JOIN #tmp_nested_countb AS tb2
		ON tb1.userid = tb2.userid 
	RIGHT JOIN (#tmp_nested_user AS tb3
		LEFT JOIN #tmp_nested_address AS tb4
			ON tb3.userid = tb4.userid)
		ON tb1.userid = tb3.userid

-- STEP 2 - Derived Table
SELECT
	tb3.userid,
	tb3.username,
	tb3.userid_tb4 AS userid,
	tb3.home_address,
	tb1.userid,
	tb1.cta,
	tb2.userid,
	tb2.ctb
FROM	
	#tmp_nested_counta AS tb1
	LEFT JOIN #tmp_nested_countb AS tb2
		ON tb1.userid = tb2.userid 
	RIGHT JOIN (SELECT tb3.userid, tb3.username, tb4.userid AS userid_tb4, tb4.home_address
				FROM #tmp_nested_user AS tb3
				LEFT JOIN #tmp_nested_address AS tb4
				ON tb3.userid = tb4.userid) AS tb3
		ON tb1.userid = tb3.userid

-- STEP 3 - No Nested Joins
SELECT
	tb3.userid,
	tb3.username,
	tb4.userid,
	tb4.home_address,
	tb1.userid,
	tb1.cta,
	tb2.userid,
	tb2.ctb
FROM	
	#tmp_nested_counta AS tb1
	LEFT JOIN #tmp_nested_countb AS tb2
		ON tb1.userid = tb2.userid 
	RIGHT JOIN #tmp_nested_user AS tb3
		ON tb1.userid = tb3.userid
	LEFT JOIN #tmp_nested_address AS tb4
		ON tb3.userid = tb4.userid

-- STEP 4 - No Right Joins
SELECT
	tb3.userid,
	tb3.username,
	tb4.userid,
	tb4.home_address,
	tb1.userid,
	tb1.cta,
	tb2.userid,
	tb2.ctb
FROM
	#tmp_nested_user AS tb3
	LEFT JOIN #tmp_nested_address AS tb4
		ON tb3.userid = tb4.userid
	LEFT JOIN #tmp_nested_counta AS tb1
		ON tb3.userid = tb1.userid
	LEFT JOIN #tmp_nested_countb AS tb2
		ON tb1.userid = tb2.userid

-- END EXAMPLES
-----------------------------------------

-----------------------------------------
-- START CLEANUP

DROP TABLE #tmp_nested_user
DROP TABLE #tmp_nested_address
DROP TABLE #tmp_nested_counta
DROP TABLE #tmp_nested_countb

-- END CLEANUP
-----------------------------------------