0

I'm building a small cinema booking system PHP web application,

The database has a Film and Showing table. (amongst others but not important)

A Showing has a date and a time, and each Showing consists of one Film

A Film can have many Showings

I'm trying to build a query that will get all the film_name, showing_date and showing_time although I want to group the results so I don't have multiple films in the result, as you can have more than one showing on the same date.

I have this SQL:

SELECT f.film_name, s.showing_date, s.showing_time
FROM film f, showing s
WHERE f.film_id = s.film_id
GROUP BY s.film_id

However it's not showing all the times for each film, just the first one. I guess there is a lot I'm missing out, and maybe I should split the showing times into a separate table, but any help would be greatly appreciated. I will most more information and diagrams if necessary.

Thanks

Aprillion
  • 21,510
  • 5
  • 55
  • 89
user1308430
  • 1
  • 5
    You say that you don't want to have multiple films in the result, but you also don't it want to show a single show time for each film... these two are contradictory; what are the results that you would like back? – Michael Fredrickson Apr 02 '12 at 16:17
  • @user1308430 better you put your tables with some data and desired output so we can help, your question is not making clear sense ... – IT ppl Apr 04 '12 at 07:51

4 Answers4

1

Assuming you want one row per film, with all showings in the same row, try:

SELECT f.film_name, group_concat(concat(s.showing_date, s.showing_time)) showings
FROM film f, showing s
WHERE f.film_id = s.film_id
GROUP BY s.film_id
  • this is for MySQL only (but it's ok, the question is for PHP web application) – Aprillion Apr 02 '12 at 16:52
  • The question is obviously MySQL only - the OP's SELECT clause includes unaggregated, ungrouped fields even though there is a GROUP BY clause in the query. –  Apr 02 '12 at 16:56
  • i see, i retagged the question to `mysql` – Aprillion Apr 02 '12 at 17:01
  • Thanks for the reply @MarkBannister, it was very helpful although I don't think that's exactly what I'm after, I think I need to separate the showing date and time into separate tables, so the showing table has the film_id fk, and a date, and then a table that has records with fields showing_id, and time. Then I should be able to get all the films with a showing on a certain date, then maybe I would code a loop that selects all the showing times for each showing, adding them to the results array, or something similar? – user1308430 Apr 03 '12 at 10:14
  • @user1308430: No, that would produce an unnecessary extra table, unless there is some other data (not so far mentioned) that is always the same for a given date and film, and does not vary by showing time. Also, you still haven't addressed Michael Fredrickson's comment from 17 hours ago; my answer was my best guess at what you were trying to achieve. –  Apr 03 '12 at 10:26
0

You cannot do what you are asking to do. Each row in your result set can only show one film name and one show time. If film A is showing 5 times, then you can either get a result set of five lines, all listing film A and the different show times, or if you group by film A, you will only get one result, and it will list the first show time.

Matt
  • 575
  • 5
  • 10
0

Based upon what you have told us, I believe what you are looking for is some way to condense each film into one row that still lists the showing dates and times properly. In order to do this, you will need to somehow collapse these rows into one row in a way that is not often used. Normall you would use some sort of function on these rows (SUM, COUNT, etc.) to give aggregate data. However, it sounds like you want to see the actual data.

To do this, there is a really helpful SO question here: Concatenate many rows into a single text string?

The second-highest rated response talks about using XML PATH, which would probably be the cleanest way of doing it if your database supports that feature. If not, look at the accepted answer (COALESCE). I would suggest putting this type of code into a scalar function that returned one field with comma-separated showtimes for you. Then you could list a film and have a list of showtimes next to the film.

Community
  • 1
  • 1
IAmTimCorey
  • 16,412
  • 5
  • 39
  • 75
0

Sorry for the confusing and maybe wasting of time, I think I have found the solution by splitting the showing times into a separate table.

I find all of the films being shown on a certain date, then loop through and select all the showing times for those films based on the showing id returned from the first query, as there will only be on showing of a film per day. I add this information to the first result per loop cycle and pass the whole data back.

There's probably better way's of doing it, but this will do for now. Thanks

user1308430
  • 1