<!--- 
 Filename: CreditReport.cfm
 Created by: Nate Weiss (NMW)
 Purpose: Demonstrates use of SQL UNION statements
--->

<html>
<head><title>Film Credits</title></head>
<body>
<h2>Film Credits</h2>
Actors and Directors, alphabetically by film.

<!--- Retrieve Films and Actors, then --->
<!--- Retrieve Films and Directors, then order everyone --->
<!--- by related Film Title and the person's last name. --->
<cfquery name="getExp" datasource="#APPLICATION.dataSource#">
 SELECT 
 f.FilmID, f.MovieTitle,
 a.NameFirst, a.NameLast, 'actor' AS Credit
 FROM 
 Films f, Actors a, FilmsActors fa
 WHERE
 f.FilmID = fa.FilmID AND
 a.ActorID = fa.ActorID
 UNION 
 SELECT 
 f.FilmID, f.MovieTitle,
 d.FirstName, d.LastName, 'director' AS Credit
 FROM 
 Films f, Directors d, FilmsDirectors fd
 WHERE
 f.FilmID = fd.FilmID AND
 d.DirectorID = fd.DirectorID
 ORDER BY
 f.MovieTitle, NameLast
</cfquery>

<!--- For each film, show the title --->
<cfoutput query="getExp" group="FilmID">
 <p><b>#MovieTitle#</b><br>
 
 <!--- Within each film, show each person involved --->
 <cfoutput>
 - #NameFirst#, #NameLast# <i>(#Credit#)</i><br>
 </cfoutput>
</cfoutput>

</body>
</html>
