仅当记录存在时，才从连接表中选择

Question

>我有以下SQL查询：

SELECT
Customers.CustomerName AS FullName,
Customers.Id AS CustomerId,
Customers.UserRoleId AS UserRoleId,
Customers.Email AS Email,
IFNULL(Customers.StudentId, '') AS CustomersStudentId,
IFNULL(Customers.MagentoId, '') AS MagentoId,
Sections.Id AS SectionId,
Sections.SectionNumber AS SectionNumber,
Sections.SectionName AS SectionName,
Courses.Id AS CourseId,
IFNULL(Courses.CourseName, '') AS CourseName,
IFNULL(Courses.CourseNumber,'') AS CourseNumber,
IFNULL(Courses.CourseDepartment, '') AS CourseDepartment,
IFNULL(Courses.Notes, '') AS CourseNotes,
IFNULL(Courses.Year, '') AS CourseYear,
IFNULL(Courses.CourseType, '') AS CourseType,
StudentsCourses.Id AS StudentsCoursesId,
IFNULL(StudentsCourses.StudentId, '') AS StudentsCoursesStudentId,
IFNULL(SiteProfile.StudentIdField, '') AS StudentIdField,
IFNULL(SiteProfile.SchoolEmailDomain, '') AS SchoolEmailDomain,
IFNULL(Orders.Id, '') AS OrderId
FROM Customers
    LEFT JOIN StudentsCourses ON Customers.Id = StudentsCourses.CustomerId
    LEFT JOIN Sections ON StudentsCourses.SectionId = Sections.Id
    LEFT JOIN Courses ON StudentsCourses.CourseId = Courses.Id
    LEFT JOIN BooksCourses ON Courses.Id = BooksCourses.CourseId
    LEFT JOIN Products ON BooksCourses.ISBN = Products.ISBN
    LEFT JOIN EbookVendors ON Products.EbookVendorId = EbookVendors.Id
    LEFT JOIN Orders ON Customers.Id = Orders.CustomerId
    LEFT JOIN SiteProfile ON Courses.SchoolCode = SiteProfile.SchoolCode
WHERE Customers.Id <> 10
    AND StudentsCourses.SectionId IS NOT NULL
    AND StudentsCourses.Delete <> 2
    AND Courses.SchoolCode = '{$criteria["school_code"]}'
    AND Courses.Year = {$criteria["year"]}
    AND Courses.CourseType LIKE '{$criteria["term"]}'

记录将始终存在于Customers表中。但有时，任何其他联接表中都没有关联的记录。

如何修改查询，以便在Customers表中只有记录时，其他SELECT和WHERE子句不会破坏结果？

编辑：

当记录仅存在于 Customers 中时，我希望该记录，并且我希望忽略与Customers表无关的WHERE子句。

如果记录存在于连接表中，我希望与该连接表相关的 WHERE 子句正常工作。

Answer 1

您需要更改 where 语句来处理空值。 喜欢这个

WHERE Customers.Id <> 10
  --  AND StudentsCourses.SectionId IS NOT NULL
    AND COALESCE(StudentsCourses.Delete,0) <> 2
    AND COALESCE(Courses.SchoolCode,'{$criteria["school_code"]}') = '{$criteria["school_code"]}'
    AND COALESCE(Courses.Year,{$criteria["year"]}) = {$criteria["year"]}
    AND (Courses.CourseType is null or Courses.CourseType LIKE '{$criteria["term"]}')

当您离开 join 并且值不存在时，这些项目将具有 null - 要仍然看到该行，您需要不要让 where 语句过滤掉这些项目。

还有另一种方法可以做到这一点，即将条件放在连接中。 因此，例如课程类型如下所示：

  LEFT JOIN Courses ON StudentsCourses.CourseId = Courses.Id and Courses.CourseType LIKE '{$criteria["term"]}'

如果这样做，则无需将过滤器添加到 where -- 它只会应用于连接，如果连接不存在，则为表列返回 null。

Answer 2

当您离开 join 时，您将在没有相应"正确"记录的字段中获得 NULL，因此您必须考虑这一点：

WHERE Customers.Id <> 10
    -- AND StudentsCourses.SectionId IS NOT NULL 
    AND (StudentsCourses.Delete <> 2 OR StudentsCourses.Delete IS NULL)
    AND (Courses.SchoolCode = '{$criteria["school_code"]}' OR Courses.SchoolCode IS NULL)
    AND ( Courses.Year = {$criteria["year"]} OR Courses.Year IS NULL)
    AND (Courses.CourseType LIKE '{$criteria["term"]}' OR Courses.CourseType IS NULL)

Answer 3

两者都错了。 不能有一个返回两种不同形状的元组的查询：如果存在，则从此处返回这些列，但如果 hat 存在，则从此处和那里返回这些列。 一个查询，一个形状。

说到这里，让我们放松一下。

只需执行外部联接，如果要联接的数据不存在（= 找不到），NULL 值将被静默、无痛地填充到指示的列中。 "人口"是一个更花哨的词。