query one record from multiple categories and display as only categories with records

[fran7]

Hi, If anyone has any pointer as to how to do this that would be great. This code gets me a list of categories only where they have artists listed in them. Instead of displaying the "category name" from tblcategories I want to change " & rs("Categorynam e") & " to a <src=""" & rs("pictureurl" ) & """> and display only one artist image from each category "pictureurl ". That could be a random artist picture or simply the first or last added in that category. In my artists table each listed artist has artistid categoryid and pictureurl.
So I need something like

select only one image from each category and display them as the number of categories active

 <%
On Error Resume Next
'set database connection
Set conn=Server.CreateObject("ADODB.Connection") 
conn.Mode = 3      '3 = adModeReadWrite
conn.Open ConnectString
strSQL="SELECT  tblCategories.CategoryID, tblCategories.CategoryName "
strSQL=strSQL & " FROM tblCategories INNER JOIN tblartists ON "
strSQL=strSQL & " tblCategories.CategoryID = tblartists.CategoryID "
strSQL=strSQL & " GROUP BY tblCategories.CategoryID, tblCategories.CategoryName"
strSQL=strSQL & " Order By tblCategories.CategoryName ASC "
Set rs=conn.Execute(strSQL)
Do While Not rs.EOF
Response.Write "<a href=""gallery5.asp?categoryid=" & rs("Categoryid") & """>" &  rs("Categoryname") & "</a>"
rs.MoveNext
Loop
rs.Close
Set rs=Nothing
conn.Close()
Set conn=Nothing
%>

Hope that makes sence for someone.
Thanks for any help
Richard

[jhardman]

That code might be specific to the db you use. What kind are you using? Access?

Jared

[fran7]

Dear jhardman, Thanks for the reply, yes its an access database. I striped it out of code that was in my program. It works fine, giving me a list of category names, and only those that have records in them, so the more categories I use the more category names appear in the list. I am prob attempting the imposible trying to get an image out of each category to represent the category instead of just the name. Getting the pictururl on its own is easy
strSQL="SELECT top 1 pictureurl FROM tblartists"
linking this with the record dependant category list is the problem.


This gets me the images - which is close - unfortunately it gets all records from the categories, and I only want one from each category. I suppose I need it to say get only one record from each category. top 1 doesnt work.

strSQL="SELECT tblCategories.CategoryID,tblCategories.Categoryname, tblartists.pictureurl "
strSQL=strSQL & " FROM tblCategories inner JOIN tblartists ON "
strSQL=strSQL & " tblCategories.CategoryID = tblartists.CategoryID"
Set rs=conn.Execute(strSQL)
Do While Not rs.EOF
Response.Write "<a href=""gallery.asp?categoryid=" & rs("Categoryid") & """><img src=""" & rs("pictureurl") & """></a>"

Thanks
Richard

[jhardman]

I just posted in access forum (see thread), let's see if they get back to me. You could easily do it programmaticall y, sort by category, display the first record, then loop through until you find a new category, display it etc. but this is a waste of time if it can be done from the db.

Jared

[jhardman]

try the first() aggregate function (suggested by NeoPa in the thread to which I linked above) - that should work.

Jared

[fran7]

Dear Jared, hey thanks a million for helping out with that great question in the access forum. It works perfectly and exactly what I wanted. I have now
strSQL="SELECT First(tP.pictur eurl) as pictureurl, categoryid FROM tblartists AS tP GROUP BY [Categoryid] "

This is great to return an image sample from categories in a database.
Thanks again
Richard