not included in join

[daiv]

Hi,

I have a join between 3 tables that finds users that are active, their email address and their backup log for the previous day. I then send them an email with the information. this works fine with the code below

[code]SELECT EmailNotify . * , FTPUsers.Firstname, FTPUsers.LastName, UserTable . *
FROM EmailNotify
LEFT JOIN FTPUsers ON EmailNotify.Username = FTPUsers.Username
LEFT JOIN UserTable ON EmailNotify.UserName = UserTable.UserID
WHERE EmailNotify.active =1
AND UserTable.Date = DATE_SUB( CURDATE( ) , INTERVAL 1
DAY )
LIMIT 0 , 30[/code]

What I need to find now is everyone who DID NOT do a backup the previous day and send an email based on that. FTPUsers has the users first and last names and other account info, UserTable includes the backup information including UserID, Date, RecordID among other information.

I am looking for a join that will give me a list of people who want email notifications everyday about the backups, but did not actually do one the previous day so I can send an email saying "no backup perfromed on this date"

.....

As I wrote that up their I figured out the answer. I do a similar join but instead of checking the date I use a NOT IN clause and give a list of the names returned from the first query, and it returns the records that were not included previously due to lack of backup activity. While looping through the first set of results I ad the UserID's to a list then can pass that into the second query.

Maybe this will help someone else. and example of the query I'd pass would look like this.

[code]SELECT EmailNotify.* , FTPUsers.Firstname, FTPUsers.LastName FROM EmailNotify LEFT JOIN FTPUsers ON EmailNotify.Username = FTPUsers.Username
WHERE EmailNotify.active =1
AND EmailNotify.UserName NOT
IN (
'David'
)
LIMIT 0 , 30[/code]


Does anyone else have another suggestion for a query that does not require the generated list?


Thanks,
Daiv

[fenway]

You could always use a LEFT JOIN ... (matching conditions) WHERE ... IS NULL, which will, by definition, return the non-matching rows.