CODEWITHSUNDEEP
mysqlsql
Sam Malayek
Sam Malayek

Reputation: 3755

Select from joined table only if record exists

I have the following SQL query:

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"]}'

Records will always exist in the Customers table. But sometimes there will be no associated records in any of the other joined tables.

How do I modify the query so that the additional SELECT and WHERE clauses don't break the results when there are only records in the Customers table?

EDIT:

When the record only exists in Customers, I want that record and I want the WHERE clauses that don't pertain to the Customers table to be ignored.

If the record exists in a joined table, I want the WHERE clause that pertains to that joined table to work.

Upvotes: 1

Views: 1588

Answers (3)

Bruce David Wilner
Bruce David Wilner

Reputation: 467

Both wrong. You can't have a query that returns two different shapes of tuples: these columns from here if this exists, but those columns from both here and there if hat exists. One query, one shape.

That having been sternly said, let's relax a little bit.

Just do an outer join, and if the data to be joined doesn't exist (= can't be found), NULL values will be silently, painlessly filled into the indicated columns. "Populated" is a fancier word for that.

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

When you left join, you're going to get NULLs in the fields for which there is no corresponding "right" record, so you have to account for that:

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)

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You need to change the where statement to deal with nulls. Like this

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"]}')

When you left join and the value does not exist you will have null for those items -- to still see the row you need to not have your where statement filter out those items.

There is another way to do it which is to put the criteria in the joins. So for example course type would look like this:

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

If you do this then you don't need to add the filter to the where -- it will only be applied to the join and will return null for the table columns if the join does not exist.

Upvotes: 1

Related Questions