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
Get the mailserver that powers this list at
http://www.coolfusion.com
