Wednesday, September 11, 2024

Query to Connect all tables of Sakila Database of MYSQL

select g1.first_name, g1.last_name, g1.film_id, g1.actor_id, g1.title, g1.release_year,

g1.language_id, g1.rating, g1.inventory_id, g1.store_id,

g3.staff_id, g3.staff_firstname, g3.staff_lastname, g3.staff_email, g3.store_id,

g3.address, g3.district, g3.city_id, g3.phone, g3.city, g3.country,

g3.payment_id, g3.rental_id, g3.customer_id, g3.amount, g3.cust_firstname,

g3.cust_lastname, g3.cust_email

from (select tt1.first_name, tt1.last_name, tt1.film_id, tt1.actor_id, tt1.title, tt1.release_year,

tt1.language_id, tt1.rating, tt2.inventory_id, tt2.store_id from (select t1.first_name, t1.last_name, t1.film_id, t1.actor_id, t2.title, t2.release_year,

t2.language_id, t2.rating from (select a.first_name,  a.last_name,

a.actor_id, b.film_id from actor a join film_actor b on a.actor_id=b.actor_id) t1

join film t2 on t1.film_id=t2.film_id) tt1 join inventory tt2 on tt1.film_id=tt2.film_id) g1

join

(select g2.staff_id, g2.staff_firstname, g2.staff_lastname, g2.staff_email, g2.store_id,

g2.address, g2.district, g2.city_id, g2.phone, g2.city, g2.country,

g2.payment_id, g2. rental_id, g2.customer_id, g2.amount, g2.cust_firstname,

g2.cust_lastname, g2.cust_email 

from

(select pppp1.staff_id, pppp1.first_name as staff_firstname, pppp1.last_name as staff_lastname, pppp1.email as staff_email, pppp1.store_id,

pppp1.address, pppp1.district, pppp1.city_id, pppp1.phone, pppp1.city, pppp1.country,

pppp1.payment_id, pppp1. rental_id, pppp1. customer_id, pppp1.amount, pppp2.first_name as cust_firstname,

pppp2.last_name as cust_lastname, pppp2.email as cust_email 

from

(select ppp1.staff_id, ppp1.first_name, ppp1.last_name, ppp1.email, ppp1.store_id,

ppp1.address, ppp1.district, ppp1.city_id, ppp1.phone, ppp1.city, ppp1.country,

ppp2.payment_id, ppp2. rental_id, ppp2. customer_id, ppp2.amount 

from

(select pp1.staff_id, pp1.first_name, pp1.last_name, pp1.email, pp1.address_id, pp1.store_id,

pp1.address, pp1.district, pp1.city_id, pp1.phone, pp1.city, pp1.country_id, pp2.country 

from 

(select p1.staff_id, p1.first_name, p1.last_name, p1.email, p1.address_id, p1.store_id,

p1.address, p1.district, p1.city_id, p1.phone, p2.city, p2.country_id 

from

(select d.staff_id, d.first_name, d.last_name, d.email, d.address_id, d.store_id,

e.address, e.district, e.city_id, e.phone 

from staff d join address e on

d.address_id=e.address_id) p1 join city p2 on p1.city_id=p2.city_id) pp1 join country pp2

on pp1.country_id=pp2.country_id) ppp1 join payment ppp2 on ppp1.staff_id=ppp2.staff_id)pppp1

join customer pppp2 on pppp1.customer_id=pppp2.customer_id) g2) g3

on g1.store_id=g3.store_id;




0 comments:

Post a Comment