Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004

    Unanswered: Select top 10 info from two Tables

    Can someone please help me, I am very new at this?

    I have a photo program written in ASP with an Access database. I have found a script that goes in a table named xlaAIGcategories and selects the last 10 categories that were created. It then lits the categories name with a link to a particular part of the program.

    I would like to also place a photo with the category name (the first photo in that category), but it is located in a different table.

    In the second table there are fields that have the same category id number “Categoryid”.

    I have two tables in the database.

    The first one is named xlaAIGcategories the second one is named xlaAIGimages.

    The first one has the following fields of interest.
    Categoryid, catname, catpath which are unique. Not two field have the same value.

    In the second table the fields of interests are Categoryid and imagefile, which are not unigue.

    My script is as follows:

    <% Dim rsLastestPostsModPhoto
    Dim adoLastestPostsModConPhoto
    Dim strLastestPostsModConPhoto
    Dim strSQLLastsPostsModPhoto

    Dim strLastestPostsModTopicSubjectPhoto
    Dim strLastestPostsModForumPathPhoto
    Dim intLastestPostsTotalPhoto

    'The path to the forum
    strLastestPostsModForumPathPhoto = "../cgi-bin/Gallery/"

    'How many new topics to show
    intLastestPostsTotalPhoto = 10

    'Default connection
    strLastestPostsModConPhoto = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../cgi-bin/Gallery/db/absoluteig.mdb")

    Set adoLastestPostsModConPhoto = Server.CreateObject("ADODB.Connection")
    adoLastestPostsModConPhoto.Open strLastestPostsModConPhoto

    Set rsLastestPostsModPhoto = Server.CreateObject("ADODB.Recordset")

    strSQLLastsPostsModPhoto = "SELECT top 10 Categoryid, catname From xlaAIGcategories ORDER BY Categoryid DESC"

    rsLastestPostsModPhoto.Open strSQLLastsPostsModPhoto, adoLastestPostsModConPhoto

    If rsLastestPostsModPhoto.EOF Then
    Response.Write "<span class=""smltext"">No New Photos Made</span>"

    Do while NOT rsLastestPostsModPhoto.EOF
    strLastestPostsModTopicSubjectPhoto = rsLastestPostsModPhoto("catname")

    Response.Write("<a href=""" & strLastestPostsModForumPathPhoto & "gallery.asp?categoryid=" & rsLastestPostsModPhoto("Categoryid") & " "">" & strLastestPostsModTopicSubjectPhoto & "</a>")


    If NOT rsLastestPostsModPhoto.EOF Then Response.Write("<br>")

    End If

    'Reset Server Objects
    Set rsLastestPostsModPhoto = Nothing
    Set adoLastestPostsModConPhoto = Nothing

  2. #2
    Join Date
    Jul 2003
    Try this:

    SELECT top 10 Categoryid, catname, (SELECT top 1 imagefile FROM xlaAIGimages img WHERE cat.CategoryID = img.Categoryid) AS imagefile From xlaAIGcategories cat ORDER BY Categoryid DESC
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Nov 2004

    It works Great

    Thank You,

    You saved me alot of time!!!!!!!!!!!!!!!!!!!!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts