SQL对具有多对多关系的多个维度进行过滤

Question

在我的 Rails 应用程序中，我有三个表来处理课程和类别之间的多对多关系

• 课程
• course_categories_courses
• course_categories

我有类别组，并且我希望允许通过以下界面按类别过滤课程列表：

位置

• 非常近
• 近
• 远

类型

• 短
• 中
• 长

要搜索近或远的中等类型我曾想过使用：

SELECT distinct courses.* 
FROM `courses` 
  inner join course_categories on 
    course_categories_courses.course_category_id = course_categories.id 
    and (
      course_categories.id in ('medium') 
      and course_categories.id in ('near', 'far')
    )

但这不起作用。有人能指出我正确的方向吗？

Answer 1

您通常希望将“关系”作为连接条件，并将值（文字）放在 where 子句中进行过滤

。加入条件 - on course_categories_courses.course_category_id = course_categories.id

例如。 where 子句 - where course_categories.id in ('near', 'far')

所以您可能想以这种方式重写您的查询。但更重要的是，这两种情况怎么可能是真的呢？

(course_categories.id in ('medium') and course_categories.id in ('near', 'far'))

这就是你的意图吗？

Answer 2

您必须连接两个表，并且您正在使用 id 字段，其中应使用类型和位置（根据您的描述）

SELECT distinct courses.* FROM `courses` 
inner course_categories_courses on course_categories_courses.course_category_id = courses.course_category_id
inner join course_categories on 
course_categories.id = course_categories_courses.id
where  (course_categories.type in ('medium') and course_categories.location in ('near', 'far'))

Answer 3

您编写的查询的语法很好。正如其他人指出的那样，它有两个不寻常的地方：

1. “内部连接上的过滤器”。 （通常在 where 完成，但也许您有自己的原因）
2. course_categories.id in ('medium')。大多数人会期望这里有一个数字，但这也许只是为了演示目的。

它“不起作用”的原因

1. 您收到了一个错误，您已将其隐藏在红宝石中，表示您没有共享
2. 它的工作原理，只是没有符合您条件的记录。

调试方法。

1. 在工作台（或其他客户端）中运行它并检查结果

2. 如果没有错误并且没有返回记录，则在工作台中运行一些查询

   从 course_categories 中选择 count(*)，其中 course_categories.id in ('medium')

   Select count(*) from course_categories where course_categories.id in ('near', 'far')

3. 如果您在 Workbench 中得到了您想要的结果，那么它可能是您的客户端（Ruby）代码，也许是错误的连接字符串？