please Inner Join

Hey Everyone,

What im trying to do is display the chosen cd or dvd that the user has selected followed by the tracks for that product which is held in a seperate database table.

The cd details are held in a table tblProducts, where the tracks are stores in tblTracks. both are linked through a productID field.

sqlQuery = "SELECT * FROM tblProducts WHERE productID=" &  prodID & "INNER JOIN tblTracks ON tblProducts.productID = tblTracks.productID"

Code (markup):

I have tried the above sql code, but to no avail, I am grateful for any help

Many Thanks

Tim Green

 

Tim,

Welcome to DP

move your where clause. your inner join needs to be a part of your from clause. this should work

sqlQuery = "SELECT * FROM tblProducts INNER JOIN tblTracks ON tblProducts.productID = tblTracks.productID WHERE tblTracks.productID =" &  prodID

Code (markup):

 

Thank you for the speedy reply. After doing the changes you said, i get the following message

Microsoft VBScript compilation  error '800a03f6'

Expected 'End'

/shop/pages/view.asp, line 39

Code (markup):

Here is my full source code. Sorry if its obvious, I never seem to be able to stop simple errors.

<!-- #include file="..\include\config.asp" -->
<%
   Dim sqlQuery 'as String
   Dim rs 'as ADODB.RecordSet
   Dim rs2 'as ADODB.RecordSet
   Dim prodID
   Dim medID

     prodID = request.QueryString("productID")
     medID = request.QueryString("MediaID")

  If(medID = 4) Then

     sqlQuery = ("SELECT * FROM tblProducts_DVD WHERE productID=" &  prodID)

     Set rs = Server.CreateObject("ADODB.RecordSet")
     Set rs = dbConn.Execute(sqlQuery)

     Response.Write("" & rs("title"))

  Else

    sqlQuery = ("SELECT * FROM tblProducts INNER JOIN tblTracks ON tblProducts.productID = tblTracks.productID WHERE tblTracks.productID =" &  prodID & "")

     Set rs = Server.CreateObject("ADODB.RecordSet")
     Set rs = dbConn.Execute(sqlQuery)
    %>
    <table width="100%">
      <tr>
        <td width="180">
          <%
          Response.Write("<img src='http://timoteius.7host.com/shop/images/cd/" & rs("productID") & ".jpg'>")%>
        </td> 
        <td align="left" valign="top">
          <%
           Response.Write("<b>Artist</b> : " & request.QueryString("Artist") & "<br>" )
           Response.Write("<b>Title :</b> " & rs("ProductTitle") & "<br>") 
           Response.Write("<b>Price :</b> £" & rs("price") & "<br>") 
           Response.Write("<b>Release Date : </b>" & rs("releaseDate"))  
           %>
         </td>
      </tr>
   </table>

Code (markup):

 

ok,

all you need to do is close your if... then statement. close it just before your table tag, like this:

  Else

    sqlQuery = ("SELECT * FROM tblProducts INNER JOIN tblTracks ON tblProducts.productID = tblTracks.productID WHERE tblTracks.productID =" &  prodID & "")

     Set rs = Server.CreateObject("ADODB.RecordSet")
     Set rs = dbConn.Execute(sqlQuery)
end if    
%>
    <table width="100%">

Code (markup):