Queries using Multiple Tables
Most useful queries in a relational database require the use of two or more tables. For the purposes of this next exercise we are going to create two tables to use in conjunction with the inventory table. The Requisition table (RecEquip) and the Requisition Detail table (ReqDetail) which is a child table for the ReqEquip table to store the details for requisition request. For every record in ReqEquip there will be one or more records in the ReqDetail table. The ReqNumber column will be the common key for the two tables.
sqlite> CREATE TABLE requisition(ReqNumber INTEGER PRIMARY KEY,Requestor VARCHAR(30) NOT NULL,Auth VARCHAR(30) NOT NULL,ReqDate CHAR(10) NOT NULL);
sqlite> INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1000,'Carl Jones','A. Robinson Mgr','2007/10/30');
sqlite> INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1001,'Peter Smith','A. Robinson Mgr','2007/11/05');
sqlite> INSERT INTO requisition(ReqNumber,Requestor,Auth,ReqDate) VALUES (1002,'Carl Jones','A. Robinson Mgr','2007/11/06');
sqlite>
Renaming Tables
Using the ALTER TABLE command, let's rename the table to "ReqEquip" and verify it with the dot command ".tables".
sqlite> ALTER TABLE requisition RENAME TO ReqEquip;
sqlite> .tables
ReqEquip inventory
sqlite>
sqlite> CREATE TABLE ReqDetail(ReqNumber INTEGER,StockNumber INTEGER,Quantity INTEGER,ItemCost REAL);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,51013,2,7.52);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,51002,4,.75);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1000,43512,4,1.52);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1001,23155,1,9.82);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1001,43111,1,3.69);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1002,51001,1,.75);
sqlite> INSERT INTO ReqDetail(ReqNumber,StockNumber,Quantity,ItemCost) VALUES(1002,23155,1,9.82);
sqlite>
Dot Notation for Table-Column Names.
Since the primary key of ReqEquip and the foreign key of ReqDetail are both called "ReqNumber" it will be necessary to be explicit regarding the table from which the column is coming from. This can be done by using the notation "table name.column name"
sqlite> SELECT ReqEquip.ReqNumber,ReqEquip.Requestor,ReqDetail.Quantity,ReqDetail.StockNumber FROM ReqEquip,ReqDetail WHERE ReqEquip.ReqNumber = ReqDetail.ReqNumber;
| ReqNumber | Requestor | Quantity | StockNumber |
| ---------- | ------------ | -------- | ------------ |
| 1000 | Carl Jones | 2 | 51013 |
| 1000 | Carl Jones | 4 | 51002 |
| 1000 | Carl Jones | 4 | 43512 |
| 1001 | Peter Smith | 1 | 23155 |
| 1001 | Peter Smith | 1 | 43111 |
| 1002 | Carl Jones | 1 | 51001 |
| 1002 | Carl Jones | 1 | 23155 |
As it happens we are only interested in items on Requisition number 1000. Using an AND clause we can exclude the other records.
sqlite> SELECT ReqEquip.ReqNumber,ReqEquip.Requestor,ReqDetail.Quantity,ReqDetail.StockNumber FROM ReqEquip,ReqDetail WHERE ReqEquip.ReqNumber = ReqDetail.ReqNumber AND ReqEquip.ReqNumber = 1000;
| ReqNumber | Requestor | Quantity | StockNumber |
| ---------- | ------------ | -------- | ------------ |
| 1000 | Carl Jones | 2 | 51013 |
| 1000 | Carl Jones | 4 | 51002 |
| 1000 | Carl Jones | 4 | 43512 |
Adjust the width of the fields to make a clearer presentation. By adding the inventory table we are able to obtain the description of the items requisitioned.
sqlite> .width 10 12 8 8 20 10
sqlite> SELECT ReqEquip.ReqNumber, ReqEquip.Requestor, ReqDetail.StockNumber, ReqDetail.Quantity ,inventory.Descrip ,ReqDetail.ItemCost FROM ReqEquip ,ReqDetail,
inventory WHERE ReqEquip.ReqNumber = ReqDetail.ReqNumber AND ReqEquip.ReqNumber = 1000 AND inventory.StockNumber = ReqDetail.StockNumber;
| ReqNumber | Requestor | StockNum | Quantity | Descrip | ItemCost |
| ---------- | ---------- | ------------ | -------- | ---------------------- | ---------- |
| 1000 | Carl Jones | 51013 | 2 | D Dry Cells 8 Pack | 7.52 |
| 1000 | Carl Jones | 51002 | 4 | AA Dry Cells 4 Pack | 0.75 |
| 1000 | Carl Jones | 43512 | 4 | 10W-30 Motor Oil, Qu | 1.52 |
Using an alias for a table name in a Query
When writing complex queries with dot notation it is sometimes helpful to reference each table with a short alias particularly if the table names are long and similar in spelling.
SELECT column_1,column_2 FROM table_name_1 AS alias_1, table_name_2 AS alias_2 ;
sqlite> SELECT a.ReqNumber, a.Requestor, b.StockNumber, b.Quantity ,c.Descrip ,
b.ItemCost FROM ReqEquip AS a ,ReqDetail AS b , inventory AS c WHERE a.ReqNumber
= b.ReqNumber AND a.ReqNumber = 1000 AND c.StockNumber = b.StockNumber;
| ReqNumber | Requestor | StockNum | Quantity | Descrip | ItemCost |
| ---------- | ---------- | ------------ | -------- | ---------------------- | ---------- |
| 1000 | Carl Jones | 51013 | 2 | D Dry Cells 8 Pack | 7.52 |
| 1000 | Carl Jones | 51002 | 4 | AA Dry Cells 4 Pack | 0.75 |
| 1000 | Carl Jones | 43512 | 4 | 10W-30 Motor Oil, Qu | 1.52 |
Cartesian Products
Cartesian Product - What happens if tables in a select query are not related properly.
The following example has two tables. One, the "managerlist" table listing store managers, their assigned store (store_assn) along with other information about the employee and the "storelist" table listing information about the store's location with the primary key, "store_number" which relates to the to "store_assn"" column in the "managerlist". The objective is to produce a list of Managers and the City and State where their assigned store is located.
sqlite> SELECT employee_id,last_name,first_name,start_date,store_assn FROM managerlist;
| employee_id | last_name | first_name | start_date | store_assn | ------------ | ---------------- | ---------------- | ---------- | ---------- | 456 | Walters | Joanna | 2006-06-14 | 23 | 532 | Niels | Matthew | 2007-02-09 | 35 | 637 | Simpson | Robert | 2008-01-21 | 27 |
| store_number | city | state |
| ------------ | ---------------- | ---------------- |
| 23 | Bowie | MD |
| 27 | Scranton | PA |
| 35 | Allentown | PA |
When dealing with two or more tables it is very important to join or relate the tables properly, if you fail to do so you will likely create what is known as a cartesian product as shown below. Notice that the records in each table have been the matched against each other resulting in nine rows. Two thirds of the result list an incorrect location for specified store number.
sqlite> .width 20 10 12 20 sqlite> SELECT (last_name||', '||first_name) AS 'Manager', employee_id AS 'Manager ID', store_assn AS 'Store Number', city||', '||state AS 'Location' FROM managerlist, storelist;
| Manager | Manager ID | Store Number | Location |
| -------------------- | ---------- | ------------ | -------------------- |
| Walters, Joanna | 456 | 23 | Bowie, MD |
| Walters, Joanna | 456 | 23 | Scranton, PA |
| Walters, Joanna | 456 | 23 | Allentown, PA |
| Niels, Matthew | 532 | 35 | Bowie, MD |
| Niels, Matthew | 532 | 35 | Scranton, PA |
| Niels, Matthew | 532 | 35 | Allentown, PA |
| Simpson, Robert | 637 | 27 | Bowie, MD |
| Simpson, Robert | 637 | 27 | Scranton, PA |
| Simpson, Robert | 637 | 27 | Allentown, PA |
The following is the correct query with a proper WHERE clause.
sqlite> SELECT (last_name||', '||first_name) AS 'Manager', employee_id AS 'Manager ID', store_assn AS 'Store Number', city||', '||state AS 'Location' FROM managerlist, storelist WHERE store_assn = store_number;
| Manager | Manager ID | Store Number | Location |
| -------------------- | ---------- | ------------ | -------------------- |
| Walters, Joanna | 456 | 23 | Bowie, MD |
| Niels, Matthew | 532 | 35 | Allentown, PA |
| Simpson, Robert | 637 | 27 | Scranton, PA |
Colin Riley -- May 2011 --