Thread: inner join

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    USA
    Posts
    226
    Rep Power
    14

    inner join


    I have a database with a products table, colors table and product colors table. I have created a SQL statement to extract the colors associated with a specific product based on ProductID. I am having a problem figuring out how to get the product name to show one time and then fill a select box with each color. Any help would be appreciated. Thank you.

    SQL Statement:
    sql = "SELECT DISTINCT Products.ProductName, Colors.ColorDescription FROM Products INNER JOIN (Colors INNER JOIN ProductColors ON Colors.ColorID = ProductColors.ColorID) ON Products.ProductID = ProductColors.ProductID WHERE Products.ProductID = " & id & ";"

    Current ASP Code that repeats the product name with each color:
    <% While Not objRec.EOF %>
    <tr>
    <td valign="top"><a href="productdetail.asp?ProductID=<%= objRec("ProductName") %>"><%= objRec("ProductName") %></a></td>
    <td valign="top"><%= objRec("ColorDescription") %></td>
    </tr>
    <% objRec.MoveNext
    Wend %>
  2. #2
  3. Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Oct 2001
    Location
    New Zealand
    Posts
    1,774
    Rep Power
    24
    Something like:

    <%
    count = 1
    While Not objRec.EOF
    if count = 1 then
    ..... code for the first time here

    else
    .... code for the rest of the times here

    end if
    count = count + 1
    objRec.MoveNext
    Wend
    %>

    ??
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    USA
    Posts
    226
    Rep Power
    14

    thanks


    Thanks for the reply. I did something like that and it works fine. Now I just need to figure out how to get another parameter working - size. I think I am going to try a group by statement.

IMN logo majestic logo threadwatch logo seochat tools logo