Help on MySQL INTERVAL

[wackyflik]

Hi, I am trying to generate a name list for:

1. expired membership

2. membership that will expire in 3 days

 

I have the SQL and technically it is working but I am a bit confusing whether my SQL statement is correct or wrong. Below is my SQL statement. Can someone give any opinion or comment on my SQL statement. Thank you.

 

SELECT *
FROM tbl_receipt tbl1
INNER JOIN (
SELECT tbl2.membership_num, MAX(tbl2.receipt_expiry_date) expiry_date
FROM tbl_receipt tbl2
GROUP BY tbl2.membership_num) tbl3
ON tbl1.membership_num = tbl3.membership_num
INNER JOIN tbl_registration tbl4
ON tbl1.membership_num = tbl4.membership_num
INNER JOIN tbl_members tbl5
ON tbl1.membership_num = tbl5.membership_num
AND tbl1.receipt_expiry_date = tbl3.expiry_date
AND tbl1.receipt_expiry_date <= CURDATE() + INTERVAL 3 DAY
ORDER BY $sortby ASC

[aschk]

At a first glance, looks ok to me. Can't really say with knowing the layout of the tables or some sample data. The only thing i'm unsure of is

"AND tbl1.receipt_expiry_date = tbl3.expiry_date " , however i can't verify this part without the aforementioned.

[wackyflik]

oh ok. it is like this. tbl_receipt will store all membership period of each members. for example, John has became our member for 5 years. meaning that, he has 5 membership periods because it is based on 1 year subscription. the only different are on start date and expiry date. therefore, i want to retrieve the latest expiry date of his membership period from tbl_receipt. what makes me a bit confusing is on this part especially the INTERVAL statement:

 

AND tbl1.receipt_expiry_date <= CURDATE() + INTERVAL 3 DAY

 

does the lines correct and able to fulfill my 2 objectives which are:

 

1. produce a name list for those who have expired their membership, and

2. produce a name list for those who will expire their membership in 3 days

 

all these names will be displayed in a single page.

 

thank you.