SQL : Using subquery to resolving query problems

When working with databases and records sometimes we find difficulties in displaying data from many tables using SQL join. For example you wish to include some aggregate columns from three tables below :

the structure of the products table :

ProductID text(15),
VendorID text(15),
Descriptions text(50),
UnitsInStock integer,
Price integer

Data in the products table :

ProductID  VendorID Descriptions                               UnitsInStock
---------- -------- ------------------------------------------ ------------
SDB0001    V0001    MS SQL Server Database 2005                           5
SDB0002    V0002    Oracle Database 10g Enterprise                        6
SDB0003    V0003    MySQL 5.0 Enterprise Edition                          8
SDBA0001   V0004    Navicat For MySQL Administration                      6
SDBA0002   V0005    Dreamcoder 4.1 For MySQL Enterprise                   4
SDBA0003   V0005    Dreamcoder 4.1 For MySQL Profesional                  8
SDBA0004   V0005    Dreamcoder 4.1 For MS SQL Server Database             4

the structure of the Purchasing table :

ProductID text(15),
VendorID text(15),
PurchaseDate date,
Quantity integer

Data in the Purchasing table :

ProductID  PurchaseDate Quantity
---------- ------------ --------
SDB0001    7/1/2008           10
SDB0002    7/1/2008           12
SDB0003    7/1/2008           12
SDBA0001   7/1/2008            8
SDBA0002   7/1/2008            3
SDBA0003   7/1/2008            8
SDBA0004   7/1/2008            8
SDB0001    7/2/2008            7
SDB0002    7/2/2008            9
SDB0003    7/2/2008            7
SDBA0001   7/2/2008            7
SDBA0002   7/2/2008            3
SDBA0003   7/2/2008            7
SDBA0004   7/2/2008            7

the structure of the sales table :

ProductID text(15),
VendorID text(15),
SalesDate date,
Quantity integer

Data in the sales table :

ProductID  SalesDate    Quantity
---------- ------------ --------
SDB0001     7/1/2008           8
SDB0002     7/1/2008           8
SDB0003     7/1/2008           9
SDBA0001    7/1/2008           4
SDBA0002    7/1/2008           1
SDBA0003    7/1/2008           2
SDBA0004    7/1/2008           5
SDB0001     7/2/2008           4
SDB0002     7/2/2008           7
SDB0003     7/2/2008           2
SDBA0001    7/2/2008           5
SDBA0002    7/2/2008           1
SDBA0003    7/2/2008           5
SDBA0004    7/2/2008           6

there is two productid that have the same values in each of sales and purchasing table. Now you want to display how many quantity bought, quantity sold, and quantity available now by joining three tables above so that query output give you the clear information concerning all products.

I will show you how this can confusing when its done by a query especially for SQL newbies. below is an example of wrong SQL statement that will give you incorrect information about the data :

SELECT a.productid, a.descriptions,
sum(b.quantity) AS prchqty,
sum(c.quantity) as salesqty, a.unitsinstock
FROM products AS a, purchasing AS b, sales c
WHERE a.productid=b.productid
and a.productid=c.productid
GROUP BY a.productid, a.descriptions, a.unitsinstock

query output:

productid descriptions                               prchqty salesqty unitsinstock
--------- ------------------------------------------ ------- -------- ------------
SDB0001   MS SQL Server Database 2005                     34       24            5
SDB0002   Oracle Database 10g Enterprise                  42       30            6
SDB0003   MySQL 5.0 Enterprise Edition                    38       22            8
SDBA0001  Navicat For MySQL Administration                30       18            6
SDBA0002  Dreamcoder 4.1 For MySQL Enterprise             12        4            4
SDBA0003  Dreamcoder 4.1 For MySQL Profesional            30       14            8
SDBA0004  Dreamcoder 4.1 For MS SQL Server Database       30       22            4

the query output above giving incorrect information for the prchqty and salesqty column. it is caused by there are more than one productid which have the same values in each of the purchasing table and the sales table so that when you join the tables using productid column the query output will display the column quantity become twice from real quantity.

to resolving this problem you can use a view and or a subquery but in this tutorial I will only show how to use subqueries to resolving the problem. below is the right SQL statement which will give a correct information about the data (note : this query will not working in previous version of MySQL 6, paradox database, and interbase 6) :

SELECT a.productid, a.descriptions,
sum(b.quantity) AS prchqty, c.salesqty,a.unitsinstock
FROM products AS a, purchasing AS b,
(select productid,sum(quantity) as salesqty
from sales
group by productid) AS c
WHERE a.productid=b.productid
and a.productid=c.productid
GROUP BY a.productid, a.descriptions,
c.salesqty, a.unitsinstock

query output :

productid descriptions                               prchqty salesqty unitsinstock
--------- ------------------------------------------ ------- -------- ------------
SDB0001   MS SQL Server Database 2005                     17       12            5
SDB0002   Oracle Database 10g Enterprise                  21       15            6
SDB0003   MySQL 5.0 Enterprise Edition                    19       11            8
SDBA0001  Navicat For MySQL Administration                15        9            6
SDBA0002  Dreamcoder 4.1 For MySQL Enterprise              6        2            4
SDBA0003  Dreamcoder 4.1 For MySQL Profesional            15        7            8
SDBA0004  Dreamcoder 4.1 For MS SQL Server Database       15       11            4

in the above query, string which in bold is subquery or which I call inline view. the subquery above will produce productid column and salesqty column which the data is taken from the sales table which then aliased as table C, so that each productid from the products table (A) will be joined to one productid in the purchasing table (B) and sales table (C).