There are 2 tables:

CREATE TABLE movie ( movieId INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description VARCHAR(255) NULL, active BOOLEAN, PRIMARY KEY (movieId) ); CREATE TABLE seance ( seanceid INT NOT NULL AUTO_INCREMENT, date DATE NOT NULL, cost INT NOT NULL, sold INT NOT NULL, active BOOLEAN, movieId INT NOT NULL, PRIMARY KEY (seanceId) );

The first table stores only movies. In the second session (many sessions for each film), cost - the cost of a ticket, sold - the number of tickets sold per session. Movie.active - show or removed from hire. Seance.active - canceled or not.

You need to make a sample: movieId, m.name, m.active, the amount of money earned by each movie, at all sessions (do not take into account money from canceled sessions), whether there will be shows with the participation of this movie today and in the future.

Here is what I get at the moment:

 SELECT m.movieId, m.name, m.active, IFNULL(SUM(s.sold*s.cost), 0) AS earned, EXISTS(SELECT s.date >= getDate()) as work FROM movie AS m LEFT JOIN seance AS s ON (m.movieId = s.movieId) WHERE s.active GROUP BY m.movieId

But it doesn’t work out with work. Help make a selection and point out errors. Thank you in advance!

    1 answer 1

    inner join to search for sums and left join to check dates in the future:

     insert into movie(name, active) values ("foo", true), ("bar",true); insert into seance(date,cost,sold,active,movieId) values ("2018-04-18",100,5,true,1), ("2018-04-19",10,2,true,1), ("2018-04-20",25,3,false,1), ("2018-04-18",101,5,false,2), ("2018-04-19",11,2,false,2), ("2018-04-20",26,3,false,2); SELECT m.movieId, m.name, m.active, IFNULL(SUM(s.sold*s.cost), 0) AS earned, checker.movieId IS NOT NULL as work FROM movie m INNER JOIN seance s USING(movieId) LEFT JOIN seance checker ON m.movieId = checker.movieId AND checker.active AND checker.date >= CURRENT_DATE GROUP BY m.movieId

    Result:

     1 foo 1 1190 1 2 bar 1 605 0
    • Thank! Everything is fine prints. True, I replaced USING with ON. - Oleg Datskevich
    • tell me, and if I add a new entry to the movie table and at the moment the new movie is not tied to any session, accordingly it will not be in the sample. Is it possible to somehow tie a query of this type: SELECT * FROM movie WHERE movieId NOT IN (SELECT DISTINCT movieId FROM session) to the general request, so that films not attached to sessions would be added to the selection? - Oleg Datskevich
    • @OlegDatskevich yes, do another left join by analogy - Lexx918