sql query help

[loaded111]

Hi all, I need some more help with a query joining data from two tables. What I want to do is to list the top film ordered.

I have an ordered table containing Customer ID and FilmID.

In the film table there is also Film ID along with title.

Example of customer id field data
FILMid customerID
501 3
501 4
503 3
504 3
504 4
504 5

I just need to get the film name from the film table, add up the filmID which has been most wanted.

Hope this is readable. Thanks in advamce for the help.

Cheers

 

[PHV]

SELECT C.FilmID, F.Title, Count(*)
FROM tblCustomer C INNER JOIN tblFilm F ON C.FilmID=F.FilmID
GROUP BY C.FilmID, F.Title
ORDER BY 3 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

replace the ORDER BY 3 DESC in PHV's query with this --

HAVING count(*) = 
 ( select max(filmcount)
     from ( select count(*) as filmcount
              from tblCustomer
            group by FilmID
          ) as filmcounts  )

untested, but i think it will work

this allows for two films having the same highest "most wanted" count

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[loaded111]

cheers guys, thats brilliant. I tried PHVs and will give the other a go.