MySQL Query Help

[JamesU2005]

Right I'll try and explain this as best as I can.

What I have is two tables, one that contains information about an image (imageid, imagetitle, imagedesc) and one that is a log (logid, imageid) of the amount of views it has recieved.

The log table only stores the image's ID for normalisation.

What I now want to do is have a page that lists the most viewed image.

What I have so far is a mysql COUNT of the imageid in the log table. This just lists and counts the imageid.

What I want to do is take the imageid that has been counted and sorted from the log table and select the relevant imagetitle from the image table.

This will then allow me to list the most popular images with their titles rather than listing the most popular images with their ids.

Hope that makes sense?

 

[GravyMonster]

What you need is a join, to relate the records of both tables together.

I only work in SQL, noy mySQL so this may not be the exact syntax you need, but it'll be close...

SELECT COUNT(LogTable.ImageID) AS [ImageHits], ImageTable.* FROM ImageTable INNER JOIN LogTable ON ImageTable.ImageID = LogTable.ImageID ORDER BY COUNT(LogTable.ImageID) DESC

 

[JamesU2005]

What you need is a join, to relate the records of both tables together.
I only work in SQL, noy mySQL so this may not be the exact syntax you need, but it'll be close...

Thanks, had to change it slightly for MySQL but the main elements are there. But I did have to add 'GROUP BY table.row' before the 'ORDER BY' otherwise it throws up an error.

Cheers!!