Nice SQL Jochem...Also to clarify, Jeff, since you said that you need to get all the fields from table one: The GROUP BY statement needs to include *all* of your non-aggregate-function fields, in a comma-delimited list. So if you're selecting t1.key, t1.name, table1.alias in addition to the max and the count fields, you would need
GROUP BY t1.key, t1.name, table1.alias Mike Mertsock Alfred University Web Team >Jeff Chastain wrote: >> >> I have two tables with a one to many relationship. I need to pull all the >> records from table one with one field in the resulting recordset being a >> count of how many corresponding records are in the second table and another >> field being the date of the latest corresponding record in the second table. >> >> Table 1 - >> - key (one) >> - name >> - alias >> - etc... >> >> Table 2 - >> - key (many) >> - thread (count how many distinct ones exist per key) >> - timeStamp (get latest per key) > >SELECT > t1.key, > COUNT(t2.key) AS recordNumber > MAX(t2.timeStamp) AS latest >FROM > t1 INNER JOIN t2 ON t1.key = t2.key >GROUP BY > t1.key > >This presumes there is at least one entry in t2 for each entry in t1. > >Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
