CODEWITHSUNDEEP
mysqlsql
raghad
raghad

Reputation: 1

Adding join or explict join

I hope is all well. I am encountering an issue writing SQL query. I did it successfully, however, now I am trying to add explicit JOIN in the query but cant seem to get it or do it properly. Any pointers or tips would be appreciated.

Thank you in advance

SELECT
  title,
  name,
  COUNT(rental_id) AS rental_count 

FROM film,
     category,
     rental,
     inventory,
     film_category

WHERE film_category.film_id = film.film_id
AND category.category_id = film_category.category_id
AND inventory.film_id = film.film_id
AND rental.inventory_id = inventory.inventory_id
AND name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')

GROUP BY name,
         title
ORDER BY name, title ;

Upvotes: 0

Views: 66

Answers (2)

WSC
WSC

Reputation: 993

I personally find it much more readable to use the explicit JOIN syntax.

Your query then looks like this:

SELECT title, name, COUNT(rental_id) AS rental_count 
FROM film
INNER JOIN inventory ON inventory.film_id = film.film_id
INNER JOIN rental ON rental.inventory_id = inventory.inventory_id
INNER JOIN film_category ON film_category.film_id = film.film_id
INNER JOIN category ON category.category_id = film_category.category_id
WHERE name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY name, title
ORDER BY name, title
;
``

Upvotes: 3

Alessio Cantarella
Alessio Cantarella

Reputation: 5201

You should use JOIN clauses to combine tables, e.g.:

SELECT title
  , name
  , COUNT(rental_id) AS rental_count 
FROM film
JOIN film_category
  ON film_category.film_id = film.film_id
JOIN category
  ON category.category_id = film_category.category_id
JOIN inventory
  ON inventory.film_id = film.film_id
JOIN rental
  ON rental.inventory_id = inventory.inventory_id
WHERE name IN ('Animation', 'Children', 'Classics', 'Comedy', 'Family', 'Music')
GROUP BY name
  , title
ORDER BY name
  , title;

Upvotes: 1

Related Questions