?
Solved

Help building an SQL Query, using count function, maybe nested selects

Posted on 2005-05-15
8
Medium Priority
?
201 Views
Last Modified: 2012-05-05
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!

0
Comment
Question by:Clever_Bob
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 14007375
if you want to return the number 2 instead of the list of movies

try:

select count(Reserve_ID) from Movie_Reserves_Pool where user_ID = 1 AND sent = 'n'
0
 
LVL 7

Author Comment

by:Clever_Bob
ID: 14007391
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.
0
 
LVL 11

Accepted Solution

by:
andrewbleakley earned 1000 total points
ID: 14007463
select count(*) from movies where title =
(

SELECT     Movies.Title
FROM         Movie_Reserves_Pool INNER JOIN
                      Movies ON Movie_Reserves_Pool.Movie_ID = Movies.ID
WHERE     (Movie_Reserves_Pool.Sent = 'y' and user_id=1  )

)

this will return the number of movies from the movies table with a title the same as a movie from the reserves table for user 1 and a sent='y'
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 14007474
in what table do you keep info about how many total copies do you have?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 14007488
0
 
LVL 25

Expert Comment

by:jrb1
ID: 14008010
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?
0
 
LVL 7

Author Comment

by:Clever_Bob
ID: 14008218
Hi all - greatly appreciate your efforts, andrewbleakley gave me nearly exactly what I needed there - thanks a million.
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 14013212
cheers
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question