<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="#REQUEST.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>