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