Complex(?) MySQL Query

[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
1 Joe joe@hotmail.com

License_ID Member_ID License_Name License_Expiration

1 1 Driver's License 2008-07-31
2 1 Driver's License 2012-07-31

so when I run a query like this:
Code:

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??

 

[hoinz]

A few thougths about it:

1) ...WHERE Member.Member_ID = License.License_ID
Shouldn't it be:
... WHERE Member.Member_ID = License.Member_ID

2) It seems you will need a 'group by' construct; have a look at the manual.
group by member_id, and compare max(license_expiration) to curdate().

hope this helps

 

[r937]

GROUP BY is not necessary

just look for a member with an expiring license where a renewed license does not exist

SELECT Member.Member_Name
     , Member.Member_Email
     , License.License_Name
     , License.License_Expiration
  FROM Member
INNER
  JOIN License
    ON License.Member_ID = Member.Member_ID
   AND DATEDIFF(License.License_Expiration, CURRENT_DATE) <= 30
LEFT OUTER
  JOIN License AS Renewed
    ON Renewed.Member_ID = Member.Member_ID
   AND DATEDIFF(Renewed.License_Expiration, CURRENT_DATE) > 30
 WHERE Renewed.License_ID IS NULL

r937.com | rudy.ca