I'm having some issues with the code below. The code executes but the problem is this.
The b.max_date column get it's data (date) from the SELECT statement after the JOIN. If the is no max(arrivalDate) found the query excludes that record from the results.
Is there way to write the SELECT so that, if there isn't a max(arrivalDate) (i.e. the value is NULL
) it would included the record with a note in the column stating "no date found"?
SELECT a.header, a.componentID, a.componentName, a.totalQuantity, a.datePosted, b.max_date
FROM customGraphicsInStock a
(SELECT componentID, max(arrivalDate) as max_date FROM customGraphicsOut group by componentID) b on a.componentID = b.componentID
WHERE a.active = 'yes'
ORDER by a.header
Thanks in advance for your help!