Returning the Correct ID with column groups and...

[AnalyzeThis]

I am trying to return the correct ID, the persons name, their , their most recent date taking a tanning course, for each type of "tanning course" . What I have below works only when the tanning courses are entered in correct order. This is not always the case however. What I think I need is something like WHERE `ID` =(SELECT MAX(...) FROM ...). But am really unsure how to go about it due to the "complex" statement.

 

SELECT `tanning_courses`.`name` AS `course_name`,max(`tanning_attendance_records`.`ID`) AS 'cnid', `tanning_attendance_records`.`group`, `tanning_attendance_records`.`name` AS `person_name`, DATE_FORMAT(max(`date`), '%m/%d/%Y' ) AS `date`,

IF(`tanning_courses`.`name` LIKE '%VEGITABLE%', DATE_ADD(max(`date`),INTERVAL 10 YEAR), IF(`tanning_courses`.`name` LIKE '%FRUIT%', DATE_ADD(max(`date`),INTERVAL 30 YEAR), IF(`tanning_courses`.`name` LIKE '%GRAIN%', DATE_ADD(max(`date`),INTERVAL 20 YEAR), '?'))) AS `expiration`

FROM `tanning_attendance_records`

LEFT JOIN `tanning_courses` ON `tanning_attendance_records`.`course` = `tanning_courses`.`ID`

WHERE `group` = '{$client["ID"]}' AND passed = 1 GROUP BY `person_name`, `course_name` ORDER BY `expiration` ASC LIMIT {$set_limit}, {$limit}"

 

 

 

Thank you in advance for any assisstance!

[Psycho]

You need to provide details regarding your table structures and the relevant fields used as foreign keys

[AnalyzeThis]

tanning_attendance_records

=====================================

ID (Primary Key) | name | group | course | date | etc.

 

tanning_courses

=====================================

ID (Primary Key) | name | description | title | etc.

 

Person attends a course on a date. The course is only valid for a certain amount of time (VEGITABLE 10 years, GRAIN 20, FRUIT 30 years). I want to return the most recent of each type of course taken by the same person and let them know when that course is going to expire and have it ordered to show that the most recent expiration dates. Because the course will be titled "GRAIN VTE 100" or "GRAIN VVT 100" there is a if statement. Please let me know if you need anything else.

 

Thanks in advance.

[AnalyzeThis]

Also there is a relationship between `tanning_attendance_records`.`course` and `tanning_courses`.`ID`

[fenway]

First, make the query without joining back to the contact name table.

[Psycho]

You have logic such that

IF(`tanning_courses`.`name` LIKE '%VEGITABLE%', DATE_ADD(max(`date`),INTERVAL 10 YEAR)

 

Are there different courses with the name 'VEGITABLE' in them? You state

I want to return the most recent of each type of course taken by the same person . . .

 

That won't work since a user *could* take two courses of the same type. Your GROUP BY is only based upon the user and the course name.

[AnalyzeThis]

Each course refered in tanning_attendance_records refers to a id of a unique course. Each uniquie course contains a name which may contain either the words "GRAIN", "VEGITABLE", or "FRUIT". If it does contain those words then they have predermined expirations dates.

 

I want to return only the most recent GRAIN, VEGITABLE, and FRUIT for each users and all the unclassified ('?'). If they have the words GRAIN, VEGITABLE, FRUIT then they have a spefific expiration date.

 

I could also rework the tanning_courses database to feature a `expiration_date` field, which is making more sense now.