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
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.
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.
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;
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With