Having trouble with a SQL Join in ColdFusion

Question

I'm trying to do something very simple in CF/SQL but just can't seem to figure out what I am doing wrong.

I have these tables:

movies       genres     actors      moviesGenres     moviesActors
------       ------     ------      ------------     ------------
movieId      genreId    actorId     id               id
title        name       fname       movie            movie
                        lname       genre            actor

I'm trying to write a query that simply lists all movies, with each movie's genre (multiple selections possible) and each movie's actors (again, multiple selections possible).

When I write the query to include just the genres, all works fine. I use for the query itself, grouping by movieId, and then a separate around the genres.

But when I try to then include the actors, everything blows up and it seems as if the grouping breaks down.

Here's the SQL query for both joins:

SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.fname, a.lname
FROM movies m
   INNER JOIN genres g ON g.genreId IN (SELECT genre FROM moviesGenres WHERE movie = m.movieId)
   INNER JOIN actors a ON a.actorID IN (SELECT actor FROM moviesActors WHERE movie = m.movieId)
ORDER BY m.title

Thanks in advance for any help!

UPDATE:

The query supplied by Leigh and Mark seems to work overall, but I am still seeing the actors displayed multiple times in the . Here is my code:

<tbody>
   <cfoutput query="variables.movieList" group="movieId">
      <tr>
         <td><a href="##">#title#</a></td>
         <td><cfoutput group="name">#name# | </cfoutput></td>
         <td><cfoutput group="actorId">#actorId# | </cfoutput></td>
      </tr>
   </cfoutput>
</tbody>

I've also tried it without grouping the final tag but that didn't work. Note that I changed the a.lName and a.fName to a.actorId for the sake of simplicity in testing.

A sample row looks like this:

The Godfather    Action | Drama |    1 | 2 | 1 | 2 |

Answer 1

Your question really has two separate issues.

1) How to write a SQL query to get the results needed for your page.

Mark and Leigh both have good solutions for this problem. Personally I prefer Leigh's.

I will just add to dump your results as you go (all here probably know that, but a good thing to have documented for posterity) as it is easy to assume that you are getting different results than you think.

Also, it is particularly relevant for the next step.

2) How to display the results correctly.

For this, you have to understand how the "group" attribute of cfoutput works. It simply check if the value for the column you selected changes from the previous row. This means that you must order by the group column or else the grouping will look wrong.

For example:

category,product

• Food, Apple
• Cleaners, Comet
• Food, Banana

If you did on the results above it would look wrong because the grouped output would show up every time the category switched. The following resultset, on the other hand, would work correctly:

• Cleaners, Comet
• Food, Apple
• Food, Banana

The upshot of this is that cfoutput group depends on the ordering and so you can only use it on one column for any one level (though, of course, you can go as many levels deep as you would like).

The solution, then, is to handle the second column grouping more manually. This can be by building up a list somewhere and then looping over it, for example.

So, for the query in your example, this would work:

<tbody>
   <cfoutput query="variables.movieList" group="movieId">
       <cfset actors = "">
        <cfoutput>
            <cfif NOT ListFindNoCase(actors,actorId)>
                <cfset actors = ListAppend(actors,actorId)>
            </cfif>
        </cfoutput>
      <tr>
         <td><a href="##">#title#</a></td>
         <td><cfoutput group="name">#name# | </cfoutput></td>
         <td><cfloop list="actors" index="actor">#actor# | </cfloop></td>
      </tr>
   </cfoutput>
</tbody>

Answer 2

You have to relate all of the tables within the join. Otherwise you end up with a cartesian product. So JOIN to the junction tables as well, instead of using them in a subquery. With the correct ORDER BY you should be able to use <cfoutput group=".."> to format the output as desired.

Not tested, but something along these lines.

SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.actorID, a.fname, a.lname
FROM movies m
        LEFT JOIN moviesGenres mg ON mg.movie = m.movieID
        LEFT JOIN genres g ON g.genreID = mg.genre
        LEFT JOIN moviesActors ma ON ma.movie = m.movieID
        LEFT JOIN actors a ON a.actorId = ma.actor
// since movie names may not be unique, do a secondary sort on movieID
ORDER BY m.title, m.movieID, g.Name, a.fName, a.lName

Edit based on comments:

As Steve mentioned, when using cfoutput group the results must be sorted and grouped the same way for the feature to work properly. See his answer for a more detailed explanation.

An alternative to approach is to use structures to generate the unique genres and actors. Note, the sql query above was modified slightly to match your updated example.

<table border="1">
<!--- group by ID in case multiple movies have the same name --->
<cfoutput query="yourQuery" group="movieID">

    <!--- use structures to save unique genres and actors --->
    <cfset genres = {}>
    <cfset actors = {}>
    <cfoutput>
        <cfset genres[name] = true>
        <cfset actors[actorID] = true>
    </cfoutput>

    <!--- display results --->
    <tr><td>#Title#</td>
        <td>#structKeyList(genres, "|")#</td>
        <td>#structKeyList(actors, "|") #</td>
    </tr>
</cfoutput>
</table>

Answer 3

Not sure I like your syntax. I'm guessing your subselects and aliasing are screwing with your results. What you are after is to join these bridge tables if I catch what you are doing. I would do something like this:

SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.fname, a.lname
FROM movies m
  INNER JOIN movieGenres mg ON m.movieID = mg.movie
  INNER JOIN genres g ON g.genreID = mg.genre
  INNER JOIN movieactors ma on ma.movie  = m.movieID
  INNER JOIN actors a on ma.actor = a.actorID
ORDER BY m.title

This would work if actors.movie and genres.movie both match movies.movieID - but normally I like to see foreign keys named better than that. For example, if Actor.movie really contains the "movieID" then name it "movieID" - otherwise I think I'm looking for

Answer 4

You can't do it with both actors and genres in the same query. Your best bet is to output just the movies with <cfoutput query="someQry" group="movieId"> and then do a query-of-queries to get the actors and genres for each movie seperately.