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.



<%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.MoveFirstdo While Not rs.eof%>
<tr><td>&nbsp;<%= rs(""entered"") %>&nbsp;
</td><td>&nbsp;<%= rs(""year"") %>&nbsp;</td><td>&nbsp;
<%= rs(""statename"") %>&nbsp;</td><td>&nbsp;
<%= rs(""capital"") %>&nbsp;</td></tr>
<%rs.movenextloop%></table>
"