MySQL select question...?

Question

I am writing a query that will select data from 3 different tables - movies, dates, and images. In the dates and movies table there will always be exactly 1 row for each movie, but in the images table there may be multiple rows for a given movie.

The results I want may contain multiple movies - all the movies that start on a specific date, but I need to limit the results to exactly one row for each movie. Right now I am getting multiple rows for the movie if I have multiple images.

For example, I want:
March2, Wild Hogs, image1
March2, Zodiac, image1

What I'm getting is
March2, Wild Hogs, image1
March2, Wild Hogs, image2
March2, Zodiac, image1
March2, Zodiac, image2

I guess I could do this by running two separate queries, but I would prefer to do it in one.

I am trying to generate a newsletter that will include one of the images for the movie. in other places I use more than one image or a randomly selected image.

Here's the site I'm working with http://www.lakeportcinema.com

Here's my query as it stands right now:
SELECT Dates.start_date, Dates.id, Dates.nopasses, Movies.title, Movies.run_time, Movies.cast, Movies.rating, Movies.rating_reason, Movies.description, Movie_art.image_url FROM Dates, Movies, Movie_art WHERE Dates.theatre_id='$theatre_id' AND Dates.id=Movies.id AND Dates.id=Movie_art.id AND ((UNIX_TIMESTAMP(Dates.start_date) <= UNIX_TIMESTAMP('$end')) AND (UNIX_TIMESTAMP(Dates.start_date) >= UNIX_TIMESTAMP('$start'))) ORDER BY Dates.start_date DESC, Movies.title

dhvrm: thanks for the imput. It sounded good, but still returns the same results.

Answer 1

I dont think its possible with a single query.
There are two ways of doing it:-

1.) Do it with two saperate queries.(Bad idea)
2.) Get the result set with duplicate entries and write your own logic to distinct them.

Manupilate this resultset:-
March2, Wild Hogs, image1
March2, Wild Hogs, image2
March2, Zodiac, image1
March2, Zodiac, image2

Answer 2

It should suffice to select a distinct movie title.

SELECT DISTINCT Movies.title, Movies.run_time, Movies.cast, Movies.rating, Movies.rating_reason, Movies.description, Dates.start_date, Dates.id, Dates.nopasses, Movie_art.image_url FROM Movies, Dates, Movie_art WHERE Dates.theatre_id='$theatre_id' AND Dates.id=Movies.id AND Dates.id=Movie_art.id AND ((UNIX_TIMESTAMP( Dates.start_date ) <= UNIX_TIMESTAMP('$end') ) AND (UNIX_TIMESTAMP( Dates.start_date ) >= UNIX_TIMESTAMP('$start'))) ORDER BY Dates.start_date DESC, Movies.title

UPDATE:

Try this:

SELECT m.title, m.run_time, m.cast, m.rating, m.rating_reason, m.description, d.start_date, d.id, d.nopasses, (SELECT a.image_url LIMIT 1)
FROM Movies AS m
INNER JOIN Dates AS d USING (id)
INNER JOIN Movie_art AS a USING (id)
WHERE d.theatre_id = '$theatre_id' AND ( (UNIX_TIMESTAMP(d.start_date) <= UNIX_TIMESTAMP('$end') ) AND ( UNIX_TIMESTAMP(d.start_date) >= UNIX_TIMESTAMP('$start') ) ) ORDER BY d.start_date DESC, m.title

Answer 3

Can you post your query and possibly a description of the tables? I'm confused as to why you would want just one image when there are multiple for a particular movie. It seems to me like you are either running the wrong query or you have the wrong table structure for what you are trying to accomplish.

Based on your new information and what it seems like you are trying to accomplish, I think the only way you are going to get this to work is by doing subqueries. If you have multiple images to select from they're going to show up in your query unless you write a subquery to select a particular image or one random image from your image table. The only other thing I could see doing is creating another table with single images for each movie that you would want to include in your newsletter.