ASP Lesson 5: More Dynamic Tables and SQL

This lesson introduces you to JOIN queries. JOINs are useful when you want to JOIN related tables to obtain a resultset that contains attributes from the JOINed tables.

The WHERE Clause

In Example 3.1 we created a query that selected the student id and grades for each student. We can use the SQL WHERE clause to limit our search. Example 5.1 contains the modified SQL statement which selects only those students who received a grade of less than 70 on Exam2:

Example 5.1: Students with Exam2 grades less than 70

<html>
<head><title>Example 5.1</title></head>
<body>
<table width="150" border="1">
<tr>
   <td width="75">SID</td>
   <td width="75">Exam 2</td>
</tr>
<%
openCN

sql="SELECT sid" & _
    ",exam2" & _
    " FROM gb_grade" & _
    " WHERE exam2 < 70"

rs.Open sql, cn
do while not rs.EOF %>
<tr>
   <td width="75"><%=rs("sid")%></td>
   <td width="75"><%=rs("exam2")%></td>
</tr>
<%
   rs.MoveNext
loop
rs.Close
closeCN
%>
</table>
</body>
</html>

Introducing... The JOIN Clause.

As promised in the introduction, it's now time to introduce the JOIN clause. Because our database (all two tables) perfectly normalized (trust me on this), the Student table must be joined with the Grade table in order to generate a report that contains both Student Name and Grades. The syntax for a join is as follows:

SELECT [field list]
FROM [first table]
[INNER | LEFT | RIGHT ] JOIN [second table]
ON [first table].[key field] = [second table].[foreign key field]

When joining tables, you are often joining them on the Key->Foreign Key fields. In our two tables, SID is the Primary Key in the Student table, and the Foreign Key in the Grade Table. Sometimes it can be far easier to use a tool like Microsoft Access' Query By Example to generate your SQL statements, especially when joining multiple tables. Example 5.2 is the first query that introducts a JOIN.

Example 5.2: Joining Student and Grade

<html>
<head><title>Example 5.2</title></head>
<body>
<table width="350" border="1">
<tr>
   <td width="75">SID</td>
   <td width="200">Name</td>
   <td width="75">Exam 2</td>
</tr>
<%
openCN

'get sid, fname, lname, and exam2
'where exam2 is less than 70

sql="SELECT gb_grade.sid " & _
    "gb_student.fname, " & _
    "gb_student.lname, " & _
    "gb_grade.exam2 " & _
    "FROM gb_grade " & _
    "INNER JOIN gb_student " & _
    "ON gb_grade.sid = gb_student.sid " & _
    "WHERE gb_grade.exam2 < 70"

rs.Open sql, cn
do while not rs.EOF %>
<tr>
   <td width="75"><%=rs("sid")%></td>
   <td width="200"><%=rs("fname") & " " & rs("lname")%></td>
   <td width="75"><%=rs("exam2")%></td>
</tr>
<%
   rs.MoveNext
loop
rs.Close
closeCN
%>
</table>
</body>
</head>