SQL : Using subquery to resolving query problems

Sunday, July 13, 2008
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).

3 comments:

Cakka said...

Fren klo MySQL dipake buat database nya VB bisa gak sih ? G lagi pengen belajar VB nih ....

Thanks

Salam,


Cakka (www.ckcybers.com)

Yanadoe said...

bisa mas via ODBC, mas buat dulu ODBC dsn nya (mas bisa baca artikel saya tentang menghubungkan delphi dg mysql) trus manggil koneksi dari VB nya ky gn :

Public nama_koneksi As ADODB.Connection

Set nama_koneksi = New ADODB.Connection

nama_koneksi.ConnectionString = "Provider=MSDASQL.1;Password=passwnya;Persist Security Info=True;User ID=usernamenya;Data Source=nama_datasourcenya"
nama_koneksi.Open


semoga bisa membantu

David said...

Nice thread. I want to say one thing that sometime even we use the correct process and query we got the incorrect result due to the corruption in database. So to repair your database during such condition you need to use SQL Recovery software.

Recent Comments

Tags Cloud

Blogumulus by Roy Tanck and Amanda Fazani