multiple sql joins in mySQL 5
I am not an expert at sql, but am trying to work towards one query for my lists, as opposed to making calls within a loop from a more basic query (i know, inefficient). I could live with the inefficiency to get this done, but sorting becomes tricky of course, so want to do in one query.
I have tables: Accounts, Devices, DeviceLogs, AccountHistory. Accounts has key AccountID and devices has DeviceID. There is a AccountID field in Devices to associate devices to accounts (so one to many Accounts>Devices). The device logs has DeviceID (one to many Devices>DeviceLogs). The AccountHistory has both AccountID and DeviceID so it can track history for both Account and Device activity (maybe bad idea).
I have this working perfectly...
SELECT d.* , a.Name as acctName, MAX( dl.CallDateTime ) AS maxdate, COUNT(bbID) as callcount
FROM Devices d
LEFT JOIN DeviceLogs dl ON d.DeviceID = dl.DeviceID
LEFT JOIN Accounts a ON d.AccountID = a.AccountID
WHERE d.AccountID = '14'
GROUP BY d.DeviceID
Now they want info from the AccountHistory, so I want to get the MAX(LogTime) for a particular DeviceID. It allows me to join using this
LEFT JOIN AccountHistory ah ON d.AccountID = ah.AccountID
adding this to the select: MAX(ah.LogTime) as lastLog
But I need the last DeviceID entry, not the last AccountID entry. So when I try this, it times out (assuming there's too much to deal with for the query)...maybe because there are many more records when looking for the DeviceID as opposed to the AccountID.
LEFT JOIN AccountHistory ah ON d.DeviceID = ah.DeviceID
times out...so my full query that is timing out is...
select d.* , a.Name as acctName, MAX( dl.CallDateTime ) AS maxdate, COUNT(dl.bbID) as callcount, MAX(ah.LogTime) as lastLog from Devices d LEFT JOIN DeviceLogs dl ON d.DeviceID = dl.DeviceID LEFT JOIN Accounts a ON d.AccountID = a.AccountID LEFT JOIN AccountHistory ah ON d.DeviceID = ah.DeviceID where d.AccountID = '14' group by d.DeviceID
I am guess maybe a subquery in here to reduce the items in the AccountHistory?
Hope this isn't too confusing, and I haven't left out any necessary info...
Any ideas? I was doing great til this last table...