SELECT DISTINCT?

S

somersbar

hey,
i have a table containing the dates and times of different films in
various cinemas. i need to get the different days that a particular
film is showing in a particular cinema. the problem is that there can
be many different shows of that film, and i only want one instance of
each date returned.
i tried using SELECT DISTINCT, but that doesnt seem to work. Is that
the right way to go about it or is there a better/correct way?

any help appreciated,

bazzer.
 
T

Tom Ellison

Dear Bazzer:

Please post the SQL of what you have so far. The easiest way to help is to
study that and suggest changes.

Tom Ellison
 
S

somersbar

SELECT FilmDetails.filmID, FilmDetails.cinemaID, Shows.showID,
Shows.showDate, Shows.seatsAvailable FROM (FilmDetails INNER JOIN Shows
ON FilmDetails.filmID = Shows.filmID) WHERE (FilmDetails.filmID = ?)
AND (FilmDetails.cinemaID = ?) AND (Shows.showDate >= ?) AND
(Shows.seatsAvailable > 0)

if a certain film is showing 5 times for example on 1 day, ill get back
that date 5 times instead of one(which is what i want).

'FilmDetails' table has 2 fields : filmID and cinemaID. this basically
tells what films are showing in which cinemas.

'Shows' table has 6 fields: showID, filmID, screenID, showDate,
showTime, seatsAvailable. this basically gives the details of each show
i.e. the film thats showing, what cinema+screen its in, the date+time
its showing.

any suggestions?

thanks again.
 
T

Tom Ellison

Dear Bazzer:

You can call a column in your table "showDate" but that does not make it a
date. You can display it as a date, but that doesn't make it a date.

Very likely it is a Date and Time. Not just a date.

I'm not trying to chide you, but just remember that constantly.

Now, here's your query edited only for my viewing preferences, so I can
study it.

SELECT D.filmID, D.cinemaID,
S.showID, S.showDate, S.seatsAvailable
FROM FilmDetails D
INNER JOIN Shows S
ON D.filmID = S.filmID
WHERE D.filmID = ?
AND D.cinemaID = ?
AND S.showDate >= ?
AND S.seatsAvailable > 0

Now, you said:

"i only want one instance of each date returned."

I'll take that as only one row for any given film at any cinema for a date.

Now, the columns showID and seatsAvailable would seem to be a problem. If a
film shows 3 times in the same day at the same theater, what is the showID?
Is showID unique for each showing of any film at a theater? Or is it
something else. This I don't know, but I'm compelled to ask.

And seatsAvailable? If there are 3 showings of "Fright at Brokeback Ridge"
at the Roxy tonight with 3, 7, and 11 seats available, what do you want in
this column. The sum perhaps?

(Sounds like a good horror film title to me!)

I'm going to take a guess. I won't show showID because I'm guessing that
doesn't make sense. I'll add up the seats, and throw in the number of
showings for good measure:

SELECT D.filmID, D.cinemaID,
S.showDate, SUM(S.seatsAvailable) AS Seats
FROM FilmDetails D
INNER JOIN Shows S
ON D.filmID = S.filmID
WHERE D.filmID = ?
AND D.cinemaID = ?
AND S.showDate >= ?
GROUP BY D.filmID, D.cinemaID, S.showDate
HAVING SUM(S.seatsAvailable) > 0

Does this do you any good?

Tom Ellison
 
S

somersbar

hey Tom,

thats fixed it. the GROUP BY statement was what i needed. i didnt
really need the seatsAvailable field so i got rid ot that and it works
perfectly now:

SELECT Shows.showDate, FilmDetails.filmID, FilmDetails.cinemaID
FROM (FilmDetails INNER JOIN
Shows ON FilmDetails.filmID = Shows.filmID)
WHERE (FilmDetails.filmID = ?) AND (FilmDetails.cinemaID = ?) AND
(Shows.showDate >= ?)

GROUP BY Shows.showDate, FilmDetails.filmID, FilmDetails.cinemaID

thanks again Tom.
 
Top