SQL Inner Join of two tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

SQL Inner Join of two tables

Author

Topic

erico
Starting Member

34 Posts

Posted - 2007-03-21 : 12:13:09

This is a continuation of a previous post but this time I'm much further along. I've been getting help (much appreicated) from others in the forum. However now that I can display one record from one table and display at least the html of two tables, but without the data, I now need to show not only the data from the two joined tables but be able to pull up a record based on it's ID number.

Example: http://test server IP address/test/GuestsLinks11.asp

Screen shot of this page:
http://entertainment.webshots.com/photo/2412808670098028130QTyyOQ

More Screen Shots of tables in design and show all rows view:

http://entertainment.webshots.com/photo/2405643530098028130VZblns

http://entertainment.webshots.com/photo/2944893350098028130XJodIk

Here is the entire ASP page code for your review:

<%
on error resume next
 set con = Server.CreateObject("ADODB.Connection")
 con.Open "File Name=E:\webservice\Kuow\Kuow.UDL"
 set recProgram = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT g.GuestID, g.ProgramID, p.ProgramID AS Expr1, g.GuestName, g.GuestDescription, p.URL, p.Description FROM dbo.T_ProgramGuests g INNER JOIN dbo.T_ProgramLinks p ON g.GuestID = p.LinkID WHERE p.ProgramID = " & Request("ProgramID")

'strSQL = "SELECT dbo.T_ProgramGuests.GuestID, dbo.T_ProgramGuests.ProgramID, dbo.T_ProgramLinks.ProgramID AS Expr1, dbo.T_ProgramGuests.GuestName, dbo.T_ProgramGuests.GuestDescription, dbo.T_ProgramLinks.URL, dbo.T_ProgramLinks.Description FROM dbo.T_ProgramGuests INNER JOIN dbo.T_ProgramLinks ON dbo.T_ProgramGuests.GuestID = dbo.T_ProgramLinks.LinkID = " & Request("ProgramID")

'strSQL = "SELECT * GuestName, Description FROM T_ProgramGuests INNER JOIN T_ProgramLinks ON T_ProgramGuests.GuestName = T_ProgramLinks.URL = " & Request("ID")
recProgram.Open strSQL,con

 ' ORIGINAL SELECT STATEMENT...strSQL = "SELECT *, ProgramID AS Expr1, GuestName AS Expr2, GuestDescription AS Expr3 FROM T_ProgramGuests"
'%>

<TABLE border="1" cellspacing="0" border="1" width="70%">

<p><b>Guest Name and Description:</b></p>
  <tr>
    <th width="35%" align="left">Guest Name</th>
		<th width="45%" align="left">Guest Description</th>
	<tr>
		<TD headers="t2"><%=recProgram.Fields("GuestName")%></TD>
	  <TD headers="t3"><%=recProgram.Fields("GuestDescription")%></TD>
  </tr>
</table>

<TABLE border="1" cellspacing="0" border="1" width="70%">
<br /><br />
<p><b>URL and Description:</b></p>
	<tr>
    <th width="35%" align="left">URL</th>
		<th width="45%" align="left">Description</th>
	<tr>
		<TD headers="t4"><%=recProgram.Fields("URL")%></TD>
	  <TD headers="t5"><%=recProgram.Fields("Description")%></TD>
<%'
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
'response.write err.Description
%>
	   
  </body>
</html>

The basic idea here is this. I want the asp page (first URL) to not only show table headers "and" their data but I also want to be able to append to the test URL an ID number so I can prove that I can read a specific record by ID.

The question here is: how do I tweak my strSQL code to make it do this?

Example of what I mean:
http://IP address of test server/test/GuestsLinks11.asp?ID=9726

I can easily get the asp page to show only one record but I need it to show multiple records AND be able to append these record numbers (which I can see when I view all rows in Ent Mgr) to my test URL to prove that I can manually request a specific one.

Any help here is much appreciated. I hope I have provided enough information for you.

Thanks in advance!

Some additional information for you...

I used the g and the p as aliases. I hate to keep typing the table name. so here is where I give the tables the alias names.

I tested using the code exactly as indicated and it displays the tables and table names but does not show table data. What am I missing?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources