[SOLVED] Complex(?) Query Help!

[gigantorTRON]

I have two database tables as follows:

 

Member_ID Member_Name Member_Email

 

License_ID Member_ID License_Name License_Expiration

 

I'm running a query that will pull the member information from the Member table to insert into an e-mail if the member's license expires within 30 days. The issue is that there are some members who have renewed their license before the expiration of their current license. The new license script inserts a new row into the license table with all pertinent information.

 

When I run my query, I need to add a check that will disregard members who have a new license even though they may have a still-active license set to expire within 30 days.

 

Another example:

Member_ID Member_Name Member_Email

1Joejoe@hotmail.com

 

License_ID Member_ID License_Name License_Expiration

11Driver's License2008-07-31

21Driver's License2012-07-31

 

so when I run a query like this:

SELECT * FROM Member, License WHERE Member.Member_ID = License.License_ID AND DATEDIFF(License.License_Expiration, CURDATE()) <= 30;

 

I get a record returned to e-mail Joe even though he has a license that's good beyond 30 days.

 

I've tried to get around the issue using COUNT() to find duplicates based on the member_id with no luck.

 

Any ideas??

[Barand]

SELECT m.*, l.licence_expiration
FROM member m
INNER JOIN licence l ON m.member_id = l.member_id
LEFT JOIN (SELECT l1.member_id FROM licence l1 
         WHERE l1.licence_expiration > CURDATE() + INTERVAL 30 DAY) as x
ON m.member_id = x.member_id
WHERE l.licence_expiration <= CURDATE() + INTERVAL 30 DAY
AND x.member_id IS NULL

 

Not tested, but the principle is find those about to expire and LEFT JOIN to those NOT about to expire. You want those that do not have a match

[gigantorTRON]

Thanks barand!!

 

I must get used to joins!