I have a table of projects and a table of comments. A project can have many comments. I want to get a list of all projects where the comment postedOn is > 30 days OR projects with no comment. What is the best way to accomplish this?
I’ve had many unsuccessful attempts; this is my latest go at it.
SELECT p.id,
p.officialStatus,
c.posted
FROM projects p
LEFT JOIN
(
SELECT max(posted) as posted,
projectid
FROM comments
WHERE DATEDIFF(day, posted, GETDATE()) > 30
OR comment IS NULL
group by projectid
) c ON p.id = c.projectid
WHERE (p.officialStatus NOT IN ('Blue', 'Canceled'))
Please use these table/column names in your answer:
SELECT projects.id FROM projects
LEFT JOIN
(SELECT comments.projectID
FROM comments
GROUP BY comments.projectID
HAVING DATEDIFF(Now(), MAX(comments.postedOn)) < 30) AS C
ON projects.id = C.projectID
WHERE C.projectID IS NULL;