Using a compound MySQL query to retrieve data from multiple tables. LIKE, BETWEEN, LEFT, Nested Select, SUM, LEFT JOIN, table ALIAS. Lets assume we have the following database tables. mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
| 6 | Paul | 2007-10-09 12:54:58 |
| 7 | Simon | 2010-01-01 00:00:00 |
| 8 | Jim | 2009-12-15 00:00:00 |
+----+--------+---------------------+
mysql> select * from Work;
+----+---------------+--------+------------+
| ID | Location | NameID | Year |
+----+---------------+--------+------------+
| 1 | New York | 3 | 2000-12-31 |
| 2 | San Francisco | 2 | 2003-12-31 |
+----+---------------+--------+------------+
mysql> select * from Salary;
+----+--------+--------+------------+
| ID | NameID | Salary | Year |
+----+--------+--------+------------+
| 1 | 1 | 45000 | 2000-12-31 |
| 2 | 2 | 43000 | 2000-12-31 |
| 3 | 1 | 46000 | 2001-12-31 |
| 4 | 1 | 51000 | 2002-12-31 |
| 5 | 1 | 53000 | 2003-12-31 |
| 6 | 3 | 21000 | 2000-12-31 |
| 7 | 3 | 28000 | 2001-12-31 |
| 8 | 3 | 29000 | 2002-12-31 |
| 9 | 3 | 35000 | 2003-12-31 |
| 10 | 3 | 35000 | 2004-12-31 |
| 11 | 6 | 35000 | 2004-12-31 |
| 12 | 6 | 37000 | 2005-12-31 |
| 13 | 8 | 23000 | 2000-12-31 |
| 14 | 8 | 26000 | 2001-12-31 |
| 15 | 8 | 26500 | 2002-12-31 |
| 16 | 8 | 29500 | 2003-12-31 |
| 17 | 8 | 32000 | 2004-12-31 |
| 18 | 8 | 39000 | 2005-12-31 |
| 19 | 7 | 39000 | 2000-12-31 |
| 20 | 7 | 39000 | 2001-12-31 |
+----+--------+--------+------------+ If we want to know how much money was earned by the people in our Name table then use the following query to find various Salary Totals for the given years. select Distinct
n.ID as NID,
n.Name,
(select sum(Salary) from Salary where NameID=NID && Year BETWEEN '2000-01-01' AND '2000-12-31') as '2000 Salary',
(select sum(Salary) from Salary where NameID=NID && left(Year,4)='2001') as '2001 Salary',
(select sum(Salary) from Salary where NameID=NID && Year BETWEEN '2000-01-01' AND '2003-12-31') as '2000-2003 Salary',
(select sum(Salary) from Salary where NameID=NID && Year > '2003-12-31') as 'Salary After 2003',
(select sum(Salary) from Salary where NameID=NID) as TotalSalary,
w.Location
from Name n
LEFT JOIN Work w ON n.ID=w.NameID;
| NID | Name | 2000 | 2001 | 00-03 | After03 | Total | Location | | 1 | Joe | 45000 | 46000 | 195000 | NULL | 195000 | NULL | | 2 | Jack | 43000 | NULL | 43000 | NULL | 43000 | SanFrancisco | | 3 | John | 21000 | 28000 | 113000 | 35000 | 148000 | NewYork | | 4 | James | NULL | NULL | NULL | NULL | NULL | NULL | | 5 | George | NULL | NULL | NULL | NULL | NULL | NULL | | 6 | Paul | NULL | NULL | NULL | 72000 | 72000 | NULL | | 7 | Simon | 39000 | 39000 | 78000 | NULL | 78000 | NULL | | 8 | Jim | 23000 | 26000 | 105000 | 71000 | 176000 | NULL | Note: It is only by using the LEFT JOIN trigger that you can retrieve the correct NULL information.
|