SELECT DISTINCT?

  • Thread starter Thread starter somersbar
  • Start date Start date
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.
 
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
 
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.
 
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
 
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.
 
Back
Top