MySQL subtract from Count Null-CodePudding

I have a movie rental Database where I have to calculate how many copies of a given movie I have available. I Have 3 tables:

a) "Movies" -Id_Movie PK, -Name, -Copies_num

b) "Client" -Id_Client PK, -Client_Name

c)"rentals" Movie_Id PK FK, Client_ID PK FK, Rental_Date, Movie_return_date

So I thought, if the movie_return_date is null that means 1 copy is rented, so that means i have (Copies_num - COUNT(NULLS of movie return date) but i cant quite figure how to do such SELECT query

SELECT Distinct M.Title, M.Copies_num as 'max_num',M.Copies_num - COUNT(IFNULL(R.Movie_return_date , NULL)) as avalible FROM rentals R LEFT JOIN Movies M ON M.Id_Movie = R.Movie_ID GROUP BY M.Copies_num, M.Title;

This kinda works but it subtracks from the wrong movie, the one that does not have any nulls

CodePudding user response：

If I understood your question correctly, you could try something like this:

SELECT Distinct M.Title, M.Copies_num as 'max_num', M.copies_num- 
COUNT(rental_date) AS available
FROM rental R right JOIN Movies M ON M.Id_Movie = R.Movie_ID 
WHERE movie_return_date is null
GROUP BY M.Copies_num, M.Title;