[eluser]Fielder[/eluser]
I've got some data in a database table but am not entirely sure how to pull it out strictly using a SQL statement and staying away from post-processing PHP loops.
Any help is appreciated. This complexity of SQL statements is way beyond my knowledge and scope.
Table:
Fields:
Code:
'term_id' <- unique pk
'addendum_id'
'startdate'
Sample Data
Code:
term_id addendum_id startdate
1 100 2009/12/01
2 100 2010/01/01
3 100 2010/02/01
4 200 2010/01/01
5 300 2010/02/01
6 400 2010/01/01
7 400 2010/02/01
8 400 2010/03/01
I want to pull out all term_ids whose 'contract_terms.startdate' = 2010/02/01. But of those, ONLY term_ids if the date is the latest one in that group of addendum_ids. And for those found, I need to know how many occurrences there were of that 'addendum_id'.
So in the example above I would see
Code:
[0] => Array
(
[term_id] => 3
[addendum_id] => 100
[startdate] => 2010/02/01
[addendum_count] => 3
)
[1] => Array
(
[term_id] => 5
[addendum_id] => 300
[startdate] => 2010/02/01
[addendum_count] => 1
)
Notice term_id 3 is pulled because it contains 2010/02/01 that is the latest date in all the 100 addendum_ids.
Notice term_id 7 is NOT pulled because although it contains a 2010/02/01 in the 400 addendum_id, there's another date later than term_id 7 (8 : 2010/03/01).
I tried the GROUP_BY but after everything is grouped I can't see inside each of those groups to evaluate the startdate or do a COUNT(addendum_id).
What I was able to do was run a pretty vanilla query to get all the term_ids, then run a foreach PHP loop for each unique addendum_id in the array to run a secondary SQL query. The results from that secondary SQL query I would do a count($results) to get the addendum_count value.
But I imagine there's a better way to do it than a bunch of post-SQL PHP loops?
