Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | Links | Search | Bio |

Guide to HTML, CSS, and JavaScript (Buffalo State College Version)


Creating a Web Form

Almost any "real world" database will have more than one (1) table. In fact, a single database may have ten (10) or more tables. For example, a school course database might have a facultyTable, studentTable, courseTable, gradeTable, and roomTable. The facultyTable and studentTable data may store some of the data in other tables such as the addressTable, emailTable, phoneTable, etc. This type information collection is a relational Database. Each table has a primary key and one (1) or more foreign keys.

Joining Multiple Tables

To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table. Tables can be "joined" without using a JOIN clause. This is referred to as a NATURAL JOIN SQL will determine if the two (2) tables have a field in common and, if so, use that field to produce the desired result set. However, as the next figure illustrates, this results in what is now as a Cartesian JOIN. The result set has thirty-six (36) rows because the facultyTable and the officeTable each hav six (6), so 6 X 6 = 36. Not usally what you would like to see in your result set.

SELECT firstName, lastName, officeNumber 
FROM facultyTable, officeTable;

FIRSTNAME            LASTNAME             OFFICE
-------------------- -------------------- ------
Don                  Teddon               BA123
Don                  Teddon               CA321
Don                  Teddon               DA231
Don                  Teddon               BA321
Don                  Teddon               CA123
Don                  Teddon               DA132
Steve                Smith                BA123
Steve                Smith                CA321
Steve                Smith                DA231
Steve                Smith                BA321
Steve                Smith                CA123

FIRSTNAME            LASTNAME             OFFICE
-------------------- -------------------- ------
Steve                Smith                DA132
Becky                Thomas               BA123
Becky                Thomas               CA321
Becky                Thomas               DA231
Becky                Thomas               BA321
Becky                Thomas               CA123
Becky                Thomas               DA132
Mikhail              Baryshinokov         BA123
Mikhail              Baryshinokov         CA321
Mikhail              Baryshinokov         DA231
Mikhail              Baryshinokov         BA321

FIRSTNAME            LASTNAME             OFFICE
-------------------- -------------------- ------
Mikhail              Baryshinokov         CA123
Mikhail              Baryshinokov         DA132
Arthur               Mitchell             BA123
Arthur               Mitchell             CA321
Arthur               Mitchell             DA231
Arthur               Mitchell             BA321
Arthur               Mitchell             CA123
Arthur               Mitchell             DA132
Alice                Alonzo               BA123
Alice                Alonzo               CA321
Alice                Alonzo               DA231

FIRSTNAME            LASTNAME             OFFICE
-------------------- -------------------- ------
Alice                Alonzo               BA321
Alice                Alonzo               CA123
Alice                Alonzo               DA132

36 rows selected.
Figure 7-1: Joining Two (2) Tables Without a JOIN Clause
Type of JoinDescription
NATURAL JOINSQL uses a common field to produce a Cartesian Join. Not recommended.
EQUI JOINThis is also know as an INNER JOIN. Returns all the records from the table using the ON clause from the left side of the equals sign (=) and only those records from the table on the right side thatmatch the specified condition.
OUTER JOINReturns all the records from the table on the right side of the equals sign (=) and only those records from the table on the left side that match the specified condition.
SELF JOINSQL creates a temporary fopy of a table and then returns all the records from the real table and the temporary table that match the specified condition.

Figure 7-2: Type of SQL Joins

The JOIN Clause

To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table.

SELECT firstName, lastName, officeNumber 
FROM facultyTable, officeTable 
WHERE facultyTable.officeid = officeTable.officeid;

FIRSTNAME            LASTNAME             OFFICE
-------------------- -------------------- ------
Don                  Teddon               BA123
Mikhail              Baryshinokov         BA123
Arthur               Mitchell             CA321
Steve                Smith                CA321
Alice                Alonzo               DA231
Becky                Thomas               DA231

6 rows selected.
Figure 7-3: The EQUI (INNER) JOIN Clause
SELECT firstName, lastName, officeNumber 
FROM facultyTable 
INNER JOIN officeTable ON facultyTable.officeid = officeTable.officeid;

LASTNAME             FIRSTNAME            OFFICE
-------------------- -------------------- ------
Teddon               Don                  BA123
Baryshinokov         Mikhail              BA123
Mitchell             Arthur               CA321
Smith                Steve                CA321
Alonzo               Alice                DA231
Thomas               Becky                DA231

6 rows selected.
Figure 7-4: The INNER JOIN Clause

The Self Join

There are times when the result you are looking for requires that you join one table with a copy of itself. For example, employees and their manager. The manager *is* an employee so to get the manager's name you need to reterive it from the employeeTable. This is what is known as a SELF JOIN. You need create a temporary copy of the employeeTable and use a table alias to refer to that temporary table in the fields of your SELECT statement.

SELECT e.ename AS Employee, m.ename AS Manager
FROM emp e, emp m
WHERE e.mgr = m.empno
ORDER BY m.ename;

EMPLOYEE             MANAGER
-------------------- --------------------
Wanjiru              Bikele
Rupp                 Bikele
Felix                Keino
Powell               Rupp
Bolt                 Shorter
Shorter              Wanjiru
Keino                Wanjiru

7 rows selected.
Figure 7-5: A Self Join

Displaying Data That Does Not Meet a Condition

There are times when the result you are looking for requires that the data you need to reterive does not meet a certain condition. For example, customers who have never placed an order.

SELECT c.customer_id AS "Customer ID", 
cust_last_name AS "Last Name", 
cust_first_name AS "First Name"  
FROM customers c
WHERE NOT EXISTS
  (SELECT * FROM orders o
   WHERE c.customer_id = o.customer_id)
ORDER BY c.cust_last_name, cust_first_name;

Customer ID Last Name            First Name     
----------- -------------------- ---------------
          8 Bogart               Sally          
          3 Buckley              Charlotte      
          1 Edwards              Sally          
         25 Gerland              Jim            
          6 Hackman              Ridley         
          2 Hitchcock            Bo             
          7 Landis               Marilou        
          9 Sen                  Dianne         
          4 Sharif               Bob            
          5 Spielberg            Sachin         
         10 Stanton              Alexander      

11 rows selected.
Figure 7-6: Result Set That Does Not Meet a Condition