Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL aggregate functions with LEFT JOIN

It's Friday afternoon* and my brain has stopped working. Normally it is me who's answering dumb SQL questions like this, sorry!

I am trying to get one table, along with the highest value of a column of another table by LEFT JOINing the latter to the former.

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes ON (jobs.id=notes.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
  AND (notes.type="complete" OR notes.type IS NULL)
GROUP BY jobs.id
ORDER BY complete_date ASC

I am trying to get all jobs that meet the WHERE jobs.… criteria, and if they have one, the timestamp of the latest type=complete note associated with that job:

Job ID     Complete Date
1          today
2          NULL
4          yesterday

Job 3 don't appear because it don't meet the jobs.status criteria. But what I actually get is:

Job ID     Complete Date
1          today
4          yesterday

Job 2 is missing, i.e. the JOIN is acting like an INNER JOIN.
I am sure it's just me having a brain-dead moment, but I can't see why my LEFT (OUTER) JOIN is not giving me all jobs regardless of the value of the note.

Specifically, users can delete notes, so potentially a complete/closed job may not have a type=complete note on it (the notes are entered when the status is changed), I am trying to catch the case when a user closes a job, adds a note, then deletes the note.

* somewhere in the east

like image 643
Nicholas Shanks Avatar asked Aug 08 '13 15:08

Nicholas Shanks


People also ask

What is the use of aggregate in MySQL?

Introduction to MySQL aggregate functions An aggregate function performs a calculation on multiple values and returns a single value. For example, you can use the AVG() aggregate function that takes multiple numbers and returns the average value of the numbers.

What is left join in SQL Server?

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2). In this tutorial we will use the well-known Northwind sample database.

How do I get the number of products in MySQL aggregate?

MySQL aggregate function – COUNT () function examples The COUNT () function returns the number of the value in a set. For example, you can use the COUNT () function to get the number of products in the products table as shown in the following query: SELECT COUNT (*) AS total FROM products;

How do I use an aggregate function?

An aggregate function performs a calculation on multiple values and returns a single value. For example, you can use the AVG () aggregate function that takes multiple numbers and returns the average value of the numbers. The following illustrates the syntax of an aggregate function:


1 Answers

Since you have the filter for the notes table in the WHERE clause the JOIN is acting like an INNER JOIN, move it to the JOIN condition:

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes 
  ON (jobs.id=notes.job_id)
  AND (notes.type="complete" OR notes.type IS NULL)
WHERE (jobs.status="complete" OR jobs.status="closed")
GROUP BY jobs.id
ORDER BY complete_date ASC;

This could also be done using a subquery, so you apply the notes filter inside the subquery:

SELECT
  jobs.*,
  n.complete_date
FROM jobs
LEFT JOIN
(
    select job_id, MAX(`timestamp`) AS complete_date
    from notes 
    where (type="complete" OR type IS NULL)
    group by job_id
) n
  ON (jobs.id=n.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
ORDER BY complete_date ASC
like image 97
Taryn Avatar answered Oct 16 '22 00:10

Taryn