Hi, We are running numerous queries which join anything from 3 - 10 tables together. Can I please ask the advice of the group as to the most efficient way to write such queries in MySQL V4. The example below has one 3 tables but 6/7 is more common.
1. using the WHERE statement to show the matching columns in each table SELECT tblnotetypes.strNoteType, Month([datTimestamp]) AS Expr1, Count(tblnotes.cntNotes) AS CountOfcntNotes, tblstaff.strFirstname, tblstaff.strSurname FROM tblnotes, tblnotetypes, tblstaff WHERE tblnotes.intNoteType = tblnotetypes.cntNoteType AND tblnotes.intAuthorStamp = tblstaff.cntStaffIndex AND tblnotetypes.strNoteType)='Enquiry' GROUP BY tblnotetypes.strNoteType, Month([datTimestamp]), tblstaff.strFirstname, tblstaff.strSurname 2. using the INNER JOIN type syntax combined with HAVING statement SELECT tblnotetypes.strNoteType, Month([datTimestamp]) AS Expr1, Count(tblnotes.cntNotes) AS CountOfcntNotes, tblstaff.strFirstname, tblstaff.strSurname FROM (tblnotes INNER JOIN tblnotetypes ON tblnotes.intNoteType = tblnotetypes.cntNoteType) INNER JOIN tblstaff ON tblnotes.intAuthorStamp = tblstaff.cntStaffIndex GROUP BY tblnotetypes.strNoteType, Month([datTimestamp]), tblstaff.strFirstname, tblstaff.strSurname HAVING (((tblnotetypes.strNoteType)="Enquiry")); Your comments would be greatly appreciated. many thanks Liz Ungless _________________________________________________ Liz Ungless BSc(Hons) MCP Developer EIC email: [EMAIL PROTECTED] tel: +44 (0) 1638 554868 fax: +44 (0) 1638 751801 web: www.eic.co.uk _________________________________________________ The content of this E-Mail is sent to the addressee only and may contain information that is confidential. Persons other than the addressee should not read, disclose, copy or otherwise distribute this message except for delivery to the addressee. This E-Mail and its attachments (if any) have been scanned by Kaspersky Antivirus, but you should satisfy yourself any files are virus-free before use.
