I have multiple queries that I want to combine into a 9 table join (probably a bad idea?) to get 4 specific columns from the tables. But I'm unsure what is the best way to do this.

I want to get the total number of genres watched by each country name.

So the columns I want are:

name from the category table

country from the country table

and a newly created column num_rentals integer

https://www.postgresqltutorial.com/postgresql-sample-database/

I'm unsure how I would approach this. Should I join all the other tables into the rental table and perform a groupby/count to get the number of rentals for that country?

After looking through the tables I see that they all tie into each other with one attribute...such as country_id in city, city_id in address, address_id in customer, customer_id in rental

SELECT rental.customer_id FROM rental AS rental;

SELECT country.country FROM country AS country;

SELECT city.country_id FROM city AS city;

SELECT address.city_id FROM address AS address;

SELECT customer.address_id FROM customer AS customer;

What's the best way do it?

CodePudding user response：

You need something like :

    select category.name, country.country, count(*)
from country 
join city on country.country_id = city.city_id
join address on address.city_id = city.city_id
join customer on customer.address_id = address.address_id
join rental on rental.customer_id = customer.customer_id
join inventory on inventory.inventory_id = rental.inventory_id
join film on film.film_id = inventory.inventory_id
join film_category on film_category.film_id = film.film_id
join category on category.category_id = film_category.category_id
group by category.name, country.country