cdemott33
asked on
T-SQL Syntax help needed. Using JOIN with NULLS
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"?
Thanks in advance for your help!
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
Join
(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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! This worked perfectly.
SELECT a.header, a.componentID, a.componentName, a.totalQuantity, a.datePosted, b.max_date
FROM customGraphicsInStock a
Join
(SELECT componentID, max(isnull(arrivalDate, '1900-01-01')) as max_date FROM customGraphicsOut group by componentID) b on a.componentID = b.componentID
WHERE a.active = 'yes'
ORDER by a.header