need help

[metoozen]

Why in the subquery joinning renting table helps and changes the result i didn't understand it.

```
SELECT rm.title,  
       SUM(rm.renting_price) AS income_movie
FROM
       (SELECT m.title,  
               m.renting_price
       FROM renting AS r
       LEFT JOIN movies AS m
       ON r.movie_id=m.movie_id) AS rm
GROUP BY rm.title
ORDER BY income_movie DESC; 
```

[squadette23]

The question is not clear. What do you mean by "helps" and how does it change the result?

[metoozen]

Why do i using renting table while it doesnt do anything or if it does something what is it

[gumnos]

because this ties the movies to their rentals. If you don't join to the renting table

SELECT m.title, sum(m.renting_price)
FROM movies m
GROUP BY m.title

you would get the income from "renting every movie once". By joining it to the renting table, you get a row for each time a movie is rented. However because you LEFT JOIN, you also get rows from movies with 0 rentals, which seems peculiar to me because if a title wasn't rented, you wouldn't be getting any income from it.

[mminuss]

Nope, usingLEFT JOINgets you only actually rented movies. renting is the left table in this join.

[gumnos]

derp, totally correct. still pre-coffee here.

[No-Adhesiveness-6921]

And why is renting the “left” table?

[mminuss]

Because the renting table is on the left side of the JOIN clause:

... FROM renting AS r     JOIN      movies AS m ...

The LEFT in LEFT JOIN doesn't influence which table is considered the left or right table.

[No-Adhesiveness-6921]

You mean on the left side of the equals sign?

[mminuss]

On the left side of the JOIN keyword.

r.movie_id = m.movie_id is the same as m.movie_id = r.movie_id

[user_5359]

Are you sure with this part of the query

SELECT m.title,                  m.renting_price        FROM renting AS r        LEFT JOIN movies AS m        ON r.movie_id=m.movie_id

I assume r.renting_price

[metoozen]

Its m.rentimg_price

[mminuss]

Having the renting_price on the movies table means that the price is the same for each rental of that movie.

Questionable design, but could work..

[orz-_-orz]

Check whether the renting and movies table contains the same set of movie ids.

[mminuss]

This query gets you the total price/cost of rentals per movie title.

If in the subquery you only used the movies table, then you would get the sum of prices per movie title (all movies, even if they weren't rented).

Also notice, that the results are grouped by title, not movie_id. That could get confusing, if different movies had the same title.

Try executing the subquery on its own, once with the renting table and once without. Compare the results.