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 comment
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 comment
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 comment
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 comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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