在ColdFusion中使用SQL Join遇到麻烦 [英] Having trouble with a SQL Join in ColdFusion

问题描述

我正在尝试在CF / SQL中做一些非常简单的事情，但似乎无法弄清我做错了什么。

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

我有这些表：

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).

当我编写查询以仅包括流派时，一切正常。我用于查询本身，按movieId分组，然后围绕流派进行单独分组。

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.

以下是两个联接的SQL查询：

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

在此先感谢您的帮助！

更新：

Leigh和Mark提供的查询似乎总体上可行，但是我仍然看到actor在中多次显示。这是我的代码：

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>

我也尝试过不对最终标签进行分组，但这没用。请注意，为了简化测试，我将a.lName和a.fName更改为a.actorId。

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.

示例行如下所示：

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

推荐答案

您必须关联联接中的所有表。否则，您最终会得到笛卡尔积。因此，也要联接表，而不是在子查询中使用它们。使用正确的ORDER BY，您应该可以使用< cfoutput group = ..> 来格式化所需的输出。

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

基于评论进行编辑：

如Steve所述，当使用 cfoutput组时，必须以相同的方式对结果进行排序和分组，以使功能正常工作。 请参阅他的答案以获取更详细的说明。

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.

方法的另一种方法是使用结构生成独特的体裁和演员。请注意，上面的sql查询已稍作修改以匹配您的更新示例。

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>

这篇关于在ColdFusion中使用SQL Join遇到麻烦的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！