problem with select query

[gregerly]

Hello, I'm not sure how to properly format a query that I need to select some database information. I have two tables, one named "appts" and another "clients". They are linked by client_id. I'm trying to write a query that will show me clients that haven't had an appointment is "X" amount of "time units". I'm doing this like:

SELECT * FROM appts JOIN clients ON appts.client_id = clients.client_id WHERE DATE(appts.appt_date) < DATE_SUB( CURDATE(), INTERVAL 1 MONTH) GROUP BY appts.client_id;

This works great if they users have already had an appointment. The problem is that users that haven't had an appointment are not being returned by the query (which I get). My question is there any way to also return those clients that haven't had an appointment yet (return those clients that have not already been booked by my system).

I realize I could just select those also in a separate query, but it would be nice to have them as part this data set? Any ideas?

Thanks!

Greg

[code green]

This works great if they users have already had an appointment. The problem is that users that haven't had an appointment are not being returned by the query.

The way around this type of problem is a LEFT JOIN.
And I would JOIN the tables the other way round in your case.

SELECT * FROM clients LEFT JOIN appts USING(client_id) 
WHERE DATE(appts.appt_date) < DATE_SUB( CURDATE(), INTERVAL 1 MONTH)
GROUP BY appts.client_id;

If your JOIN has to be the other way then your WHERE clause from appts table will have to accept NULLs

OR DATE(appts.appt_date) IS NULL

I have no idea what VoraAnkit is trying to tell you.

[gregerly]

Your the man Code Green, this is just what I was looking for. I was making it more complicated than it had to be. Thanks for your answer!

Greg

[gregerly]

Ok, I'm back and this is getting a little old. The suggestion that Code Green gave was spot on, but I realized it was still not quite what I need. The code shouldn't be a problem, rather it seems the logic I'm having a problem with.

I need to create a report that lets the user know which of their clients have been in for an appointment in X amount of INTERVAL. The query I have now basically says, "Select all clients where the appointment date is less than the specified time interval". What I really need is "Select all client where the MAX(appointment date) is less than the specified time interval.

The problem is when I use MAX() inside the where, I get a mysql error. How would I write the query to only look at the max appointment date? My code is below:

Currently the sql looks like this:

"SELECT * FROM clients_tbl JOIN appt_table USING(client_id) WHERE appt_table.appointment_date < DATE_SUB ( CURDATE(), INTERVAL 1 WEEK );" 

Ideally what I need is:

"SELECT * FROM clients_tbl JOIN appt_table USING(client_id) WHERE MAX(appt_table.appointment_date) < DATE_SUB ( CURDATE(), INTERVAL 1 WEEK );" 

If anyone has any ideas, I'm all ears.

Thanks again guys!

Greg

[gregerly]

SOLVED: because I gave up. I don't think what I want to do can be done with MySQL (not because of mysql, but because of my DB set up). I've decided to just do the date comparisons with PHP rather than mysql. It's less efficient, but I don't see a way to do it with straight mysql.

Thanks for your help guys!

Greg