I've been thinking on this one and it's stumped me.
I need to query a user table (dhcpclients) for all users (31 records) and include 25 rows for each user from another table (logs). So the end result is 775 rows- 25 for each 31. Make sense?
Attached is the code I'm working with.
The query that makes up the "join" in there works fine but obviously stops at the limit- which is this case would be one user record with 25 of the log table records. the problem is if I move the limit up it's not going to move to the next user until it's exhausted all the matches in the log table. I need it to quit after 25 and move to the next user.
SELECT dhcp.machinename, log1.* FROM dhcpclients dhcp
SELECT DATE(logs.date) AS 'Date', SUBSTRING_INDEX(SUBSTRING_INDEX(logs.request,'://',-1),'/',1) AS 'Site',
COUNT(logs.request) AS 'Hits',
FORMAT(SUM(logs.elapsed)/1000,2) AS 'Xfr Time(sec)', FORMAT(SUM(logs.bytes)/1024,2) AS 'KB Txfrd',
FORMAT((((SUM(logs.bytes)/1024)/1024)*8)/(SUM(logs.elapsed)/1000),2) AS 'Mbits/s', logs.ip
WHERE logs.request NOT LIKE '[%' AND SUBSTRING_INDEX(SUBSTRING_INDEX(logs.request,'://',-1),'/',1) NOT LIKE '%knothe.com%'
AND DATE(logs.date) = DATE(NOW())
GROUP BY DATE(logs.date),logs.ip, SUBSTRING_INDEX(SUBSTRING_INDEX(logs.request,'://',-1),'/',1)
ORDER BY DATE(logs.date) DESC, COUNT(logs.request)DESC, SUM(bytes)/1024 DESC
LIMIT 25 )
log1 ON log1.ip = dhcp.ipaddress
ORDER BY dhcp.machinename ASC