Sometimes when working with databases and records, we have find difficulties in displaying data from many tables using SQL especially using join syntax. For example you want to include some aggregate columns from three tables below :
the structure of the products table :
1 2 3 4 5 | ProductID text(15), VendorID text(15), Descriptions text(50), UnitsInStock integer, Price integer |
Data in the products table :
1 2 3 4 5 6 7 8 9 | 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 :
1 2 3 4 | ProductID text(15), VendorID text(15), PurchaseDate date, Quantity integer |
Data in the Purchasing table :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 :
1 2 3 4 | ProductID text(15), VendorID text(15), SalesDate date, Quantity integer |
Data in the sales table :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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 :
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 8 9 | 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 output of query 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) :
1 2 3 4 5 6 7 8 9 10 | 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 :
1 2 3 4 5 6 7 8 9 | 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 |
You can see query above, 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)
Tags: MySQL, Oracle, PL/SQL, Problems, Query, Resolving, sql server, Subquery, Using