Okay, so I've never used joins in SQL before, so I don't know what the fuck I'm doing. I've got two tables with the relevant fields posted below.

TABLE FILMS
film_id - primary key for this table

TABLE SHOWTIMES
showtime_id - primary key for this table
film_id - which item from table A this refers to
showtime - datetime

I need to be able to get the first (chronologically) showtime from SHOWTIMES for each entry in FILMS. I can do that with a second query for each film, obviously, but if there's a way to use a JOIN to get it at the same time that'd be great. Is that even what joins are used for? Anybody know how to do this?

2/27/2011 10:49:47 AM

mysql?

select distinct f.film_id, * from films as f left join showtimes as s on f.film_id = s.film_id

or something like that.

2/27/2011 10:59:03 AM

oh chronologically. adding:

order by s.showtime ASC

to the end should do that. i think. totally off the cuff though, i haven't tested it at all.

2/27/2011 11:04:36 AM

2/27/2011 11:30:08 AM

select f.flim_id, min(s.showtime)
from films as f left join showtimes as s on f.film_id = s.flim_id
group by f.film_id

assuming you actually need to join the tables. If you really only want the film id and earliest show time, you can do the same thing from just the showtimes table.

2/27/2011 11:31:31 AM

SELECT film_id, min(showtime)
FROM Showtimes
WHERE showtime >= NOW()
GROUP BY film_id

If you need additional fields from Films other than film_id, then you can do this:

SELECT F.film_id, min(S.showtime), ...add additional fields from films here...
FROM Films AS F
LEFT JOIN Showtimes AS S ON S.film_id = F.film_id
WHERE S.showtime >= NOW()
GROUP BY F.film_id

[Edited on February 27, 2011 at 12:06 PM. Reason : .]

2/27/2011 11:45:19 AM

Okay, that works perfectly.

Question part two--how would I select the elements from the films table that have NO corresponding entry in the showtimes table?

For a little bit of background, if it isn't obvious, I'm trying to sort films in order of their first play date. Some films don't have a set release date yet, though, so they don't have anything in the showtimes table. I'd like to list the films with showtimes in ascending order of their first showtime (that part is working) and then list everything that doesn't have a showtime.

I need to get a book on SQL or something.

2/27/2011 8:16:23 PM

Select * From films where film_id not in (select film_id from showtimes)

2/27/2011 8:45:19 PM

Fuck emergency responders. You guys are the REAL heroes.

I really appreciate it.

2/27/2011 9:34:04 PM

for the novice sql..er, this site can be a huge help

http://sqlzoo.net/

if you're just getting started, go there and run some queries. they will teach you all the ins and outs and its more hands-on than cracking open a book.

3/1/2011 10:25:31 AM

Is this thread about Galaxy?

3/1/2011 10:58:47 AM

Yes and no.

Yes in that I'm playing around with new ideas for our web site because, while it's pretty, what we have doesn't do the best job of conveying information; no in that I'm just dicking around in my free time and it's not likely to ever be something that the public will see.

3/1/2011 12:57:40 PM