detailed explanation of SQL select statement required

[thepreacher]

The one thing i struggle with in SQL is when i have to write select data involving more than a table and i have to use the aggregate functions and condition.

Is there a pattern as to what should come first or the order of in which the aggregate functions and the conditions should be used?

eg.

TableA - Personnel details
TableB - non attendance record
TableB - attendance review record
TableC - Doctors Report

A staff is due for review is they have had 3 absences in 3 months rolling or more than 5 absences in 6 months rolling.

I need to Select all staff who are due for review ( both 3 and greater than 5 months rolling)

assumption Payroll number is a primary field in TableA and foreign in all the other tables. Obviously a staff can have more than one record in all the other tables apart from TableA.

All tables apart from TableA have a field called Date_of_entry which is the actual date the record was entered and is used as the primary field. This can be different from the actual date the data for the record occurred.

Please any answer should also explain what the query is and why so that i can understand. I don't just want the code i need to understand as well. If there is any material somewhere that will be beneficial please let me know.

PS. Also if you can touch on nested select statements with regards to aggregate functions and conditions. I know i am asking a lot.
This post is also on http://www.sitepoint.com/forums/showthread.php?p=3879360#post3879360

[VGR]

naaaah it's not difficult once you solve some problems :
- you've synonym columns in some tables, thus any WHERE clause will be rejected as ambiguous
- define what is the data you need : which column from which table, and so on.

Then take advantage of MySql's brilliant handling of agregates (not all columns in the SELECT clause have to be in the GROUP BY clause) and apply those simple rules :

- alias all your tables
- build a SELECT asking only for the data you need (no "SELECT *")

like this :

select a.cust_id, b.cust_name, c.somedate, d.somedate AS somedate2, sum(c.whatever) AS somWhatever
from customers AS a LEFT JOIN cust_details AS b ON (b.id=a.id) LEFT JOIN othertable AS C ON (c.id=a.somefield) LEFT JOIN finaltable AS d ON (d.id=a.otherkey)
WHERE c.somedate>='2008-01-01' and c.somedate <=now() and d.somedate>=c.somedate
GROUP BY c.whatever
ORDER BY whatever ASC, whatever DESC
[LIMIT n,m]

that's all...