1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

please Inner Join

Discussion in 'C#' started by timoteius, Apr 12, 2006.

  1. #1
    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
    SEMrush
    Tim Green
     
    timoteius, Apr 12, 2006 IP
    SEMrush
  2. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Tim,

    Welcome to DP :D

    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):
     
    vectorgraphx, Apr 12, 2006 IP
  3. timoteius

    timoteius Guest

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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):
     
    timoteius, Apr 12, 2006 IP
  4. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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):
     
    vectorgraphx, Apr 12, 2006 IP