Help building an SQL Query, using count function, maybe nested selects
Hello Team, I hope you can help
The Setup:
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:
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!
The Setup:
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:
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!
ASKER
Thanks Andrew, that cerrtainly helps but it still only returns the number of movies that this user has reserved (but not been sent).
We still need to return the number of movie titles that are available for dispatch.
We still need to return the number of movie titles that are available for dispatch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in what table do you keep info about how many total copies do you have?
something wrong with your DB design:
see as example:
http://www.databaseanswers.org/data_models/video_rental_store/video_rental_dezign.htm
see as example:
http://www.databaseanswers.org/data_models/video_rental_store/video_rental_dezign.htm
Maybe I'm missing something. Say movie 1, 2, and 3 are all the same title. Movie 1 and 2 are checked out, and now I want a copy. What happens? Only 1 entry is in the reserves pool?
ASKER
Hi all - greatly appreciate your efforts, andrewbleakley gave me nearly exactly what I needed there - thanks a million.
cheers
try:
select count(Reserve_ID) from Movie_Reserves_Pool where user_ID = 1 AND sent = 'n'