[squibby]

I have a problem. I have a list of students. The students can buy language classes. Some classes are bought by time period , and some by hour.Both classes have an expiration date - TD_Updated_End

I want to generate a list of all active students in the school now. The problem is student can buy different types of classes in different quantities.

I have the following query:

SELECT DISTINCT `students`.`STU_ID` , `students`.`STU_Chinese_Name` , `students`.`STU_English_Name` , `students`.`STU_Gender` , `students`.`STU_Phone_Number` , `students`.`STU_Email` , `source`.`SRC_Source_Name` , `staff`.`STA_English_Name` ,
CASE WHEN DATEDIFF( TD_Updated_End, CURDATE( ) ) >0
THEN 'Active'
ELSE 'Inactive'
END AS
STATUS
FROM source
INNER JOIN students ON source.SRC_ID = students.STU_Source
INNER JOIN staff ON students.STU_Course_Consultant = staff.STA_ID
INNER JOIN transaction_master ON students.STU_ID = transaction_master.TM_Customer_ID
INNER JOIN transaction_detail ON transaction_master.TM_TRX = transaction_detail.TD_ID
INNER JOIN services ON transaction_detail.TD_Service_ID = services.SER_ID

This works ok and brings back the following type of data:

163 李时皓 Steve Male Referral Inactive
164 方能 Evan Male Walk In Active
164 方能 Evan Male Walk In Inactive
165 张赫恬 Sunny Female Referral Active


You can see that EVAN has bought 2 classes one is still going and one has finished and so i get 2 lines in the results.

What i want is to somehow - check to see IF the student has one Active class then show active and just show 1 line (ignoring the inactive one). and if 2 or 3 inactive just show 1 line and show inactive.


i hope you guys can understand my problem.Thanks

[Dormilich]

I’d try with a WHERE clause that excludes any inactive classes. something like WHERE TD_Updated_End > NOW()

[squibby]

yes thanks for that, i have done that before and it does work. However if a student has 2 active classes then there would still be 2 rows of their details showing.

Also i would like to be able to filter the table in browser using jquery. so if all are active i cant filter between active/inactive students.

[Dormilich]

squibby, on 03 February 2012 - 10:43 AM, said:

yes thanks for that, i have done that before and it does work. However if a student has 2 active classes then there would still be 2 rows of their details showing.

of course. they are different datasets, after all.