Jump to content

ChuckP

New Members
  • Posts

    2
  • Joined

  • Last visited

    Never

Everything posted by ChuckP

  1. Ok, I'm going to try to explain it better. table: inspections __________________________________________________________________ | inspection_id | inspection_timestamp | inspection_score | account_id_FK | ---------------------------------------------------------------------------- | 1 | 11111111111 | 8 | 1 | ---------------------------------------------------------------------------- | 2 | 22222222222 | 6 | 1 | ---------------------------------------------------------------------------- | 3 | 11111111111 | 5 | 2 | ---------------------------------------------------------------------------- table: accounts ___________________________ | account_id | account_name | ------------------------------- | 1 | Adam's Auto | ------------------------------- | 2 | Bob's Bakery | ------------------------------- | 3 | Carl's Cats | ------------------------------- | 3 | David's Ducks| ------------------------------- I want the records for ALL accounts, matched with their highest inspection score and the correlated inspection date, if available. If they don't have any inspections then their info should still be in the result set. Example desired result set for above tables: 3 results: _____________________________________________________________________________________________ | account_id | account_name | inspection_id | inspection_timestamp | inspection_score | account_id_FK | ---------------------------------------------------------------------------------------------------------- | 1 | Adam's Auto | 2 | 22222222222 | 6 | 1 | ---------------------------------------------------------------------------------------------------------- | 2 | Bob's Bakery | 2 | 11111111111 | 5 | 2 | ---------------------------------------------------------------------------------------------------------- | 3 | Carl's Cats | null | null | null | null | ---------------------------------------------------------------------------------------------------------- | 4 | David's Ducks| null | null | null | null | ---------------------------------------------------------------------------------------------------------- Note that while Adam's Auto has two inspections, only the most recent inspection is included in the data set (although it has a lower score). The closest I've gotten is everything above EXCEPT that the inspection score doesn't match the inspection date, so I get the most recent inspection date with some other date's inspection score, which obviously is worthless. I've been doing this for hours and I'm going crazy, help please . Thanks, chuck Pearce
  2. I am trying to select a single entry from the "process_account_inspections" where the column "timestamp" is the Max (resulting in the most recent inspection). My code is below, however I can't find how to select the max timestamp with each row returned. Any suggestions? Currently it just returns the lowest entry in the table. I tried to GROUP BY accounts.account_id HAVING MAX(InspectionDate) but that didn't work. SELECT accounts.*, franchisees.*, offices.*, process_account_inspections.`timestamp` AS InspectionDate, process_account_inspections.inspection_score AS InspectionScore, process_account_inspections.satisfactory AS Satisfactory FROM accounts INNER JOIN franchisees ON accounts.franchisee_id_FK = franchisees.franchisee_id INNER JOIN offices ON franchisees.office_id_FK = offices.office_id LEFT JOIN process_account_inspections ON accounts.account_id = process_account_inspections.account_id_FK GROUP BY accounts.account_id ORDER BY process_account_inspections.`timestamp` ASC Thank you, Chuck
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.