Jump to content

Selecting Max Valued Result From Joined Table


ChuckP

Recommended Posts

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

 

Link to post
Share on other sites

Just order descending and limit to 1:

 

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` DESC LIMIT 1

Link to post
Share on other sites

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

Link to post
Share on other sites

I didn't read this carefully, but it sounds like the standard groupwise maximum problem... you need to do this in 2 steps.

Link to post
Share on other sites

Yeah, as far as I know it would have to be 2 loops:

 

<?php
  $accounts = mysql_query("SELECT * FROM `accounts`");
  while($account = mysql_fetch_assoc($accounts)){
    print_r($account);
    $inspections = mysql_query("SELECT * FROM `inspections` WHERE `account_id_FK` = '{$account['account_id']}' ORDER BY `inspection_score` DESC");
    $inspection = mysql_fetch_assoc($inspections);
    print_r($inspection);
  }
?>

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.