Welcome Guest, Not a member yet? Register   Sign In
SQL Statement Suggestions
#1

[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:
Code:
'contract_terms'
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?
#2

[eluser]mddd[/eluser]
You can use a subquery. First you sort by addendum_id, then by date (most recent first). So you have the most recent date as the first result of each group. Then if you take that result and do a GROUP BY addendum_id, you get the first date from each group in your result. It's easy to check if that is your correct date.

Code:
SELECT term_id, addendum_id, startdate, COUNT(term_id) addendum_count
FROM
   (SELECT term_id, addendum_id, startdate
   FROM contract_terms
   ORDER BY addendum_id, startdate DESC)
WHERE startdate = '20100201'
GROUP BY addendum_id

I think that should do it..
#3

[eluser]Fielder[/eluser]
Thanks I'll give it a shot. Does it make sense to put data manipulation into the SQL queries instead of post processing the returned results in php loops? Is it more efficient and better practice? Is there a point where the queries can be so complicated/nested they don't run well?

Also I noticed you didn't include slashes in your date. Can I do that and Mysql will know it's a date?

Thanks.
#4

[eluser]mddd[/eluser]
Yes if queries get too complicated you will see slowdown on the database. But doing one query is MUCH better than many. As long as the complexity is within reason, I would go for the single query. It is more about selection than manipulation here. Myqsl is well optimised for it.

Of you store the date in a DATE field, Mysql will understand pretty much any logical date format you throw at it. I like to use yyyymmdd gut others will work.
#5

[eluser]Fielder[/eluser]
How do I return the number of times a particular addendum_id occured in the query? For instance, in the above '100' would return a count of 3 and '300' a count of 1.

Do I put a
Code:
COUNT(addendum_id) Count
in my SELECT statement? And this will return the number of each addendum_id in the subquery?




Theme © iAndrew 2016 - Forum software by © MyBB