0

I have these tables and I are trying to calulate how many sold seats there is for each movie. I still can't get it to work and I only get the total for sold seats for all the movies.

Movie:
moveID
mName

Prog:
progID
moveID
showID

Show:
showID
show start
show stop
soldSeats
progID

This is one of the codes have been working on:

SELECT mName, SUM(soldSeats)
FROM movie, show
WHERE movie.movieID = prog.movieID
AND prog.showID = show.showID
GROUP BY mName

Can anybody help?

user1823846
  • 15
  • 3
  • 2
    is this the sql you have been using? It refers to prog in the where but not as a table. – Dave Richardson Nov 14 '12 at 14:19
  • I see that I forgot Prog when I wrote it here. I'm sure I used it in the code I used before. Anyway code above works for me now, I do not know what happened, I've spent hours on this little problem :| – user1823846 Nov 14 '12 at 16:38

1 Answers1

1

You are not joining with the table Prog. Also, you should really use proper explicit joins in your queries.

SELECT M.mName, SUM(S.sodSeats) SoldSeats
FROM Movie M
LEFT JOIN Prog P
    ON M.movieID = P.movieID
LEFT JOIN Show S
    ON P.showID = S.showID
GROUP BY M.mName
Lamak
  • 69,480
  • 12
  • 108
  • 116
  • Can you qualify your last sentance 'you should really use proper explicit joins' - I've seen that mentioned before, what's wrong with the way he's done it? – Dave Richardson Nov 14 '12 at 14:32
  • 1
    @DaveRlz http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where – Lamak Nov 14 '12 at 14:35
  • Thanks for the reply, but I did not get this code to the way it should, it listed all the combination of soldseats, not summarized. However the code I wrote over with the "Prog" is working now. – user1823846 Nov 14 '12 at 16:46