identical record names and query

[fran7]

Hi, I have a problem quering by name as I have identical names. Its fine when I query by category or postcardid but when you click their link which is queried by name of course the query cannot distinguish between them and so goes to the first record under that name.

Is there a way to query that says 'where "artist" = the postcard of that artist' and therefore unique?

Any suggestions would be great
Thanks
Richard

description=Request("artist")
If description <> "" Then

	Set connPostCardSoft=Server.CreateObject("ADODB.Connection") 
	connPostCardSoft.Open PostCardSoftConnectString
	Set rsCard=Server.CreateObject("ADODB.Recordset")
	rsCard.CursorLocation = 3
    SQLQuery="Select tblGreetingcategories.categoryname,tblGreetingPostCards.categoryid,tblGreetingPostCards.downloadsix,tblGreetingPostCards.titleimageeight,tblGreetingPostCards.titleimagesix,tblGreetingPostCards.largeimageseven,tblGreetingPostCards.mediumimagesix,tblGreetingPostCards.priceimagesix,tblGreetingPostCards.dimensionssix,tblGreetingPostCards.yearimagesix,tblGreetingPostCards.largeimagesix,tblGreetingPostCards.PostCardID,tblGreetingPostCards.DefaultHeadline,tblGreetingPostCards.CardDescription,tblGreetingPostCards.artist,tblGreetingPostCards.gallery,tblGreetingPostCards.newimageseven,tblGreetingPostCards.titleimagesix,tblGreetingPostCards.titleimageseven,tblGreetingPostCards.DefaultMessage,tblGreetingPostCards.Author,tblGreetingPostCards.PictureURL,tblGreetingPostCards.ThumbnailURL,tblGreetingPostCards.dimensionsseven,tblGreetingPostCards.ExtraThree,tblGreetingPostCards.downloadseven,tblGreetingPostCards.NewImageOne,tblGreetingPostCards.NewImagetwo,tblGreetingPostCards.NewImageThree,tblGreetingPostCards.NewImageFour,tblGreetingPostCards.NewImageFive,tblGreetingPostCards.NewImageSix,tblGreetingPostCards.NewImageSeven,tblGreetingPostCards.NewImageEight,tblGreetingPostCards.BackgroundImageURL,tblGreetingPostCards.ThumbnailHTML from tblGreetingcategories,tblGreetingPostCards Where tblGreetingcategories.categoryid = tblGreetingpostcards.categoryid and artist='" & description & "'  and tblGreetingpostcards.CategoryID <> " & 60 & " and tblGreetingpostcards.CategoryID <> " & 63 & " Order By BackgroundImageURL DESC"
    rsCard.Open SQLQuery, connPostCardSoft
   	rsCard.PageSize = 2
   	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

[jhardman]

the term is "sub-query", it should be something like this:

[code=asp]"SELECT * FROM authors WHERE authorid = (SELECT authorid FROM postcards WHERE postcardID = 543)"[/code]Does this make sense? The query within the parentheses is executed first and should return the author ID which is then inserted in the outer query. Let me know if this helps.

Jared

[fran7]

Dear Jared, Thats exactly what I meant.
The trouble is I dont know which id each author has that is queried so cannot put WHERE postcardID = 543. if i do and its not the right one then it returns no entry. I have tied

WHERE postcardid  = '" & request("postcardid ") & "'

but get the error
Data type mismatch in criteria expression.

"SELECT * FROM authors WHERE authorid = (SELECT authorid FROM postcards WHERE postcardID = 543)"

I have tried many combinations but to no avail.
Thanks
richard

[jhardman]

Dear Jared, Thats exactly what I meant.
The trouble is I dont know which id each author has that is queried so cannot put WHERE postcardID = 543. if i do and its not the right one then it returns no entry. I have tied

WHERE postcardid  = '" & request("postcardid ") & "'

but get the error
Data type mismatch in criteria expression.

"SELECT * FROM authors WHERE authorid = (SELECT authorid FROM postcards WHERE postcardID = 543)"

I have tried many combinations but to no avail.
Thanks
richard

I just checked to make sure this is the right syntax, and it is correct. Do you know how to run a query analyzer? This might make things simpler.

The old standby asp approach is this:
1- try entering a static number in the sub-query that is definitely a good postcardID. If that doesn't work, I don't know what to tell you, it should work with little problem.

2- If #1 worked, try using your request("postca rdid") and print out the query. Does it still look OK?

If you have trouble running a query analyzer, please let me know.

Jared