how should I write this query

[gregerly]

I have posted this over at the MySQL forum with no responses, so I thought I might pick the brains of the PHP gurus... I'm having an issue with an SQL query. Heres what I have:

2 tables, client_tbl and appt_table, they join using client_id

I'm trying to write a report that will show me:

a) Any clients that have not had an appointment in X amount of time.
This would be done using DATE_SUB(CURDATE(), INTERVAL $time $units) and comparing that against the appointment date in the appt_table.

b) Any clients that have not ever been scheduled for an appointment.
This would be done by finding clients that have no records in the appointment table.

I'm hoping this can be done in one query, but I'm thinking it's not possible. What I have now works only if a client has previously been scheduled (so part a above works), but if a client has never been scheduled, they are not included in the query results.

Any idea how I can select something if it's NOT in another table?

My current SQL is below:

SELECT * FROM client_tbl LEFT JOIN appt_table USING (client_id) WHERE appt_table.appt_date_time < DATE_SUB( CURDATE(), INTERVAL 2 week)

The obvious thing here is that if a client hasn't ever been scheduled there is no appt_date_time in the database so the where fails and the client isn't included in the result. This is where i need help!

Again, I apologize for the double post, but I wasn't getting any takers in the other forum. Mods, please feel free to edit / remove this post if I'm showing bad form.. :)

Greg

[Markus]

SQL isn't my strong point, so please excuse this reply if it's utter bull.

Anyway, I'll explain this as best as I can.

I have tables projects, project_releases and project_changelogs.

Now, there are going to be times when I need to know if a project (taken from the projects table) has any releases, without running SQL queries and counting the results. So, I simply have a flag in the projects table has_release. When a project is initially created (no releases) this is defaulted to 0, and when a release is added I then update this to 1.

Maybe this sort of 'flag' could be of help to you.

[gregerly]

Right, I see the theory here. I will need to see how I can work that in. Thanks for the idea!