CodeAve.com/ASP - Two Table Join

CodeAve.com - ASP - Database Table Display

ASP
·Read/Write
·Script Writers
·Database Display
·Response Objects
·Server Variables
·Random Events
·Articles
·Miscellaneous
·Contents
·What's New?
JavaScript
CSS
HTML
Maps
Links
Mail List
Search
Sitemap

Two Table Join

Outputting data from two tables is almost as easy as displaying from one. This example utilizes two tables with state information on them. The first table (state_order) contains state abbreviations (state), state names (statename) the year each state was admitted into the union (year) and the numeric order they entered in (entered) . The second table (state_capitals) contains state abbreviations (state), state names (state) and state capitals (capital). The where statement (state_order.state = state_capitals.state) looks to make a match on the field that each table has in common to ouput the correct capital with all the info from the state_order table.

•Text View

•Print View

•Mail this Link

• View the Data

<%
accessdb="state_info"
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)
Set rs = Server.CreateObject("ADODB.Recordset")

sql = "select state_order.*, state_capitals.capital"
sql = sql & " from state_order, state_capitals where state_order.state = state_capitals.state"
sql = sql & " order by entered; "

rs.Open sql, cn
%>

<%= sql %>
<p>
U.S. States in order admitted into the Union
</p><table>
<%
rs.MoveFirst
do while not rs.eof
%>
<tr>
<td> <%= rs("entered") %> </td>
<td> <%= rs("year") %> </td>
<td> <%= rs("statename") %> </td>
<td> <%= rs("capital") %> </td>
</tr>

<%
rs.movenext
loop%>
</table>