Search Marketing

   
   

Nested Select Compound Queries On Multiple Database Tables

Tue Feb 02, 2010 12:41 am
<<     >>
Comments: 0 Views: 3869

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.



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (20 + 1)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc