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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 SQL Inner Join of two tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

erico
Starting Member

34 Posts

Posted - 03/21/2007 :  12:13:09  Show Profile  Reply with Quote
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?
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000