select statement where

[fran7]

Hi, I am having trouble with a select statement and not sure how to phrase it.


I have many links on one page like this.

I need that when you click on one postcardid link all the other postcardids associated with the author of that link are displayed on another page page.

href="artschooldirectory.asp?PostCardID=<%=rsCard("PostCardID")%>"

This is the code on the other page. At the moment it only displays one result, ie the individual postcardid from the link. Each postcardid is by an author. I need it to display all the postcardids from the one author.
I dont know if that is clear but any help woulr be great.
Thanks
richard

lngPostCardID=Clng(Request("PostCardID"))
If lngPostCardID <> "" Then
 
    Set connPostCardSoft=Server.CreateObject("ADODB.Connection") 
    connPostCardSoft.Open PostCardSoftConnectString
    Set rsCard=Server.CreateObject("ADODB.Recordset")
    rsCard.CursorLocation = 3
    SQLQuery="Select PostCardID,DefaultHeadline,CardDescription,DefaultMessage,Author,PictureURL,BackgroundImageURL,ThumbnailHTML,Rnd([PostCardID])  From tblGreetingPostCards where PostCardID=" & Clng(lngPostCardID) & " ORDER BY Author"
    rsCard.Open SQLQuery, connPostCardSoft
       rsCard.PageSize = 600
       intPageCount = rsCard.PageCount
 
       If rsCard.EOF=True Then
        Response.Write "<p>No artists found in database for this category."
        Response.End
    End If
End If

[DrBunchman]

Hi Richard,

Try changing your SQL string to something like the following:

SQLQuery = " SELECT PostCardID, DefaultHeadline, CardDescription, DefaultMessage, Author, PictureURL, BackgroundImageURL, ThumbnailHTML, Rnd([PostCardID]) FROM tblGreetingPostCards WHERE AuthorID IN (SELECT AuthorID FROM tblGreetingPostCards WHERE PostCardID = " & Clng(lngPostCardID) & ")"

This query is selecting every row where the AuthorID is the same as the AuthorID of the selected postcard (I've assumed a column called AuthorID which you can obviously change to fit your db).

Does this make sense? Let me know how it goes.

Dr B

[fran7]

Dear Dr B,
Tht worked a treat, thanks for that and understanding exactly what I wanted.
Richard