Grouping JOIN Clauses In SQL

code-1.cfm

<!--- Create temp tables. --->
<cfsavecontent variable="strCreateSQL">

	DECLARE @company TABLE (
		id INT,
		name VARCHAR( 30 )
	);

	DECLARE @contact TABLE (
		id INT,
		name VARCHAR( 30 ),
		company_id INT
	);

	DECLARE @phone TABLE (
		name VARCHAR( 30 ),
		contact_id INT
	);

	INSERT INTO @company
	(
		id,
		name
	)(
		SELECT 1, 'Nylon Technology' UNION ALL
		SELECT 2, 'Edit.com' UNION ALL
		SELECT 3, 'HotKoko'
	);

	INSERT INTO @contact
	(
		id,
		name,
		company_id
	)(
		SELECT 1, 'Maria Bello', 1 UNION ALL
		SELECT 2, 'Christina Cox', 1 UNION ALL
		SELECT 3, 'Julia Stiles', 2 UNION ALL
		SELECT 4, 'Julie Ensike', 3

	);

	INSERT INTO @phone
	(
		name,
		contact_id
	)(
		SELECT '123-456-1890', 1 UNION ALL
		SELECT '123-456-5555', 4
	);

</cfsavecontent>

code-2.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		( ct.name ) AS contact_name,
		( p.name ) AS contact_phone
	FROM
		@company c

	LEFT OUTER JOIN
		@contact ct
	ON
		c.id = ct.company_id

	LEFT OUTER JOIN
		@phone p
	ON
		ct.id = p.contact_id
</cfquery>

code-3.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		( ct.name ) AS contact_name,
		( p.name ) AS contact_phone
	FROM
		@company c

	LEFT OUTER JOIN
		@contact ct
	ON
		c.id = ct.company_id

	LEFT OUTER JOIN
		@phone p
	ON
		ct.id = p.contact_id

	WHERE
		p.name IS NOT NULL
</cfquery>

code-4.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		( ct.name ) AS contact_name,
		( p.name ) AS contact_phone
	FROM
		@company c

	LEFT OUTER JOIN
		@contact ct
	ON
		c.id = ct.company_id

	INNER JOIN
		@phone p
	ON
		ct.id = p.contact_id
</cfquery>

code-5.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		( ct.name ) AS contact_name,
		( p.name ) AS contact_phone
	FROM
		@company c

	LEFT OUTER JOIN
		(
				@contact ct
			INNER JOIN
				@phone p
			ON
				ct.id = p.contact_id
		)
	ON
		c.id = ct.company_id
</cfquery>

code-6.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		t.contact_name,
		t.contact_phone
	FROM
		@company c

	LEFT OUTER JOIN
		(
			SELECT
				ct.company_id,
				( ct.name ) AS contact_name,
				( p.name ) AS contact_phone
			FROM
				@contact ct
			INNER JOIN
				@phone p
			ON
				ct.id = p.contact_id
		) AS t
	ON
		c.id = t.company_id
</cfquery>

code-7.cfm

<!--- Query for contacts. --->
<cfquery name="qContact" datasource="#REQUEST.DSN.Source#">
	<!--- Create temp tables. --->
	#PreserveSingleQuotes( strCreateSQL )#

	<!---
		Query for companies and contacts, but ONLY return
		contacts if there is an associated phone number.
	--->
	SELECT
		c.id,
		c.name,
		( ct.name ) AS contact_name,
		( p.name ) AS contact_phone
	FROM
		@phone p
	INNER JOIN
		@contact ct
	ON
		p.contact_id = ct.id
	RIGHT OUTER JOIN
		@company c
	ON
		ct.company_id = c.id
</cfquery>