Help building an SQL Query, using count function, maybe nested selects
Posted on 2005-05-15
Hello Team, I hope you can help
I have three tables, one contains Movies (ID, title) another contains Users (User_ID, Name etc) and the third is called "Movie_Reserves_Pool". Each record in the Movie_Reserves_Pool contains information about a movie that has been reserved (Reserve_ID, User_ID, Movie_ID, sent). It's fairly simple so far I think.
The query I'm having trouble with forms part of the dispatch screen for a video library, I'd like the query to return the number of movies that are currently available to be sent to the current member.
In other words in need to return a count of the number of movies that....
1) Are in this particular users reserves pool (i.e each record that their ID number appears in the "User_ID" column of the "Movie_Reserves_Pool" table
2) There is at least one copy of this movie in the "movies" table. This is a bit tricky because of the design of the database. Copies of the same movie will have different "Movie_ID" numbers but will have an identical "Title". To maek things easier, only the first copy of a movie is ever entered into the Movie_Reserves_Pool table (so if there are three copies of a movie #ID 40, #ID 70 and #ID 90, only #70 is entered in the reserves table.
So for example,
- the user may have 5 movies reserved (e.g. Movie numbers 20, 30. 40, 50 and 60) and these records appear in the Movie_Reserves_Pool table
- 2 of these movies (20, 30) have been returned already ("sent" = "r" in the Movie_Reserves_Pool table)
- another of these movies is currently checked out to another user ("sent" = "y" in the Movie_Reserves_Pool table against movie number 40)
- another movie (40) has two copies checked out (#40 and #70) but a third copy (with the same "Title" in the "Movies" table is available (#90)
So the number returned should be "2" because there are two different titles that are available to be sent to this member.
HERE IS WHAT I'VE GOT SO FAR (NOT MUCH!)
This returns a list of movies from the Movie_Reserves_Pool which user #1 has reserved but not been sent...
select movie_ID from Movie_Reserves_Pool where user_ID = 1 AND sent = 'n'
Hope you can help!