<!---
	Query for all the songs - singer data will be replicated
	across the records.
--->
<cfquery name="allSongs" datasource="testing">
	SELECT
		s.id,
		s.singerID,
		s.name,

		<!--- Get artist information. --->
		( sgr.name ) AS singerName
	FROM
		song s
	INNER JOIN
		singer sgr
	ON
		s.singerID = sgr.id
	ORDER BY
		singerName ASC,
		name ASC
</cfquery>


<!--- Query for all singers. --->
<cfquery name="singers" dbtype="query">
	SELECT
		( singerID ) AS id,
		( singerName ) AS name
	FROM
		allSongs
	GROUP BY
		id,
		name
	ORDER BY
		name ASC
</cfquery>


<cfoutput>

	<!---
		Loop over all the singers - for each singer we will
		re-query the data for the songs sung by the given singer.
	--->
	<cfloop query="singers">

		<!--- Query for the songs sung by the current singer. --->
		<cfquery name="songs" dbtype="query">
			SELECT
				id,
				name
			FROM
				allSongs
			WHERE
				singerID = #singers.id#
			ORDER BY
				name ASC
		</cfquery>


		#singers.name# [#songs.recordCount# Songs]:<br />

		<!--- Output things singer's songs. --->
		<cfloop query="songs">

			-- #songs.currentRow#) #songs.name#<br />

		</cfloop>

		<br />

	</cfloop>

</cfoutput>