Need help with my queries!

[Pasqualz]

Hi all,
I have two tables with the following structure: (I've only included relevant fields!)

projects
ID, date, status (date is the date this project was started, status can be new, hold, old.)

projectfollowups
ID, projectID, date (date is the date the followup was added.)

Since I have many concurrent projects going on, I need to create an automated script that will run two queries that will check all projects for the following two conditions:

1) Show project IDs for projects that are more than 7 days old, and have no followups yet. I think I got this one.

SELECT * FROM projects LEFT JOIN projectfollowups ON projects.ID = projectfollowups.projectID WHERE projectfollowups.projectID IS NULL and now( ) > (projects.date + INTERVAL 7 day)

2) Show project IDs for projects that have status = new and with their most recent followup occuring [b][u]more than[/u][/b] 7 days ago.

Select projects.ID FROM projects left join projectfollowups ON projects.ID = projectfollowups.projectID WHERE projects.status = 'new' and now( ) < (projectfollowups.date + INTERVAL 7 day) group by projects.ID

This query seems to give me the opposite of what I'm looking for. It gives me the project IDs for projects that have their most recent followup occuring [b][u]Less Than[/u][/b] 7 days ago. Is there a qualifier for this query that basically asks for all exceptions to it, or is there just a better way to write it?

Any help is appreciated! I'm desperate to get this working so that I can automate my project management.

Thank You

[fenway]

Well, you have < instead of > for the 2nd query, so it will be less than.  Also, you should write your query so that you're comparing against the db column, in case an index could be used.  E.g. projects.date < NOW() - INTERVAL 7 DAY.

[Pasqualz]

fenway, thank you for the followup and clarification about comparing against the db column. You are correct that I'm using < instead of >. I tried this because, when I use > I get [u]all[/u] projects that have followups occuring more than 7 days ago, but all I want to know is which projects have their [u]most recent[/u] followup occuring more than 7 days ago and I need [u]only[/u] the ID and date of that most recent followup. This way, I can send an automated alert email that tells me the date of the last followup. I know I'm being a bit confusing due to my lack of knowledge in MySQL, nut hopefully someone can help me out!

[btherl]

It might be easier to get all followups older than 7 days and then filter them in php.  Doing it in mysql is not all that easy.  You can get mysql to order them by date, which will make the php filtering quite simple.

[Pasqualz]

btherl, I was afraid of that, but thanks for the info. I was slowly working my way towards doing most of the grunt work in PHP , but was holding out hope that one query could solve the problem. Maybe someone else still can show me a way to do it in a query?!? Since it's very easy to query the db to get all followups that were posted WITHIN the last 7 days, I'm still hopeful that someone can show me a nifty trick to get the exceptions to that query, which would give me what I want! Anyone???

[fenway]

[quote author=Pasqualz link=topic=122311.msg504809#msg504809 date=1168813482]
fenway, thank you for the followup and clarification about comparing against the db column. You are correct that I'm using < instead of >. I tried this because, when I use > I get [u]all[/u] projects that have followups occuring more than 7 days ago, but all I want to know is which projects have their [u]most recent[/u] followup occuring more than 7 days ago and I need [u]only[/u] the ID and date of that most recent followup. This way, I can send an automated alert email that tells me the date of the last followup. I know I'm being a bit confusing due to my lack of knowledge in MySQL, nut hopefully someone can help me out!
[/quote]

Well, "most recent" is a different problem all together, you'll need to find the desired record first, then check the date; but you should definitely do all of this in MySQL.