Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2007, 01:24 PM
Registered User
 
Join Date: Sep 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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...

 
Old January 18th, 2007, 02:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there...

just an idea, but how about joining only devices and accounthistory to see if it timeout???
maybe there is too much data on AccountHistory?? can you use the Query analizer to see if you will do it better with an index???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
 
Old January 18th, 2007, 02:41 PM
Registered User
 
Join Date: Sep 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply!

Just joining the Devices and AccountHistory gave me acceptable query times. I have indexes on any AccountID or DeviceID fields.

My particular test call, returns two devices, one with 120 accounthist records, and the other with 727 records.

 
Old January 18th, 2007, 02:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

ok.. witch language are you using??

maybe the dataset (or recordset) can be configurable to have more timeout time??

also did you try the query analyzer???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
 
Old January 19th, 2007, 02:43 PM
Registered User
 
Join Date: Sep 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have to apologize, I put in the wrong mysql version in the subject line of this thread!
mysql 4.1.10 and php 5

I will try and find a query analyser.

Seems to me though that it was a flaw in the logic of the statement, and if it's really this big of a process, I should rethink and find a different way to get this information. I can loop through the records and make calls to the db to get this info, and then wrap the result in js so they can sort client-side. But with sql, I thought there was pretty well always a way to get the info in one call...so here I am.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Joins for multiple tables Jinn SQL Server 2000 2 November 5th, 2006 11:39 PM
optimizing MySQL table joins - script running slow crmpicco MySQL 7 January 31st, 2006 10:54 AM
Multiple Inner Joins msmagied Classic ASP Databases 0 October 6th, 2004 05:11 PM
Multiple Joins in Multiple Table Search query pookster Access 4 September 23rd, 2004 03:04 PM
multiple self joins and sum juan@jbstech.net SQL Language 3 May 25th, 2004 06:03 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.