1119 [Sakia] Inventory + Rental

대여 중인 비디오 

-> rental.return_date is null

반납 날짜가 null인 것

SELECT inventory_id, customer_id
FROM rental
WHERE return_date IS NULL;

SELECT i.*, t.*
FROM inventory i
	LEFT OUTER JOIN 
	(SELECT inventory_id, customer_id, rental_date
	FROM rental
	WHERE return_date IS NULL) t
	ON i.inventory_id = t.inventory_id;

 

+ film.title

SELECT t1.*, t2.*
FROM 
	(SELECT
		i.inventory_id
		, i.film_id
		, f.title
		, i.last_update
		, i.store_id
	FROM inventory i INNER JOIN film f
	ON i.film_id = f.film_id) t1
	LEFT OUTER JOIN 
	(SELECT inventory_id, customer_id, rental_date
	FROM rental
	WHERE return_date IS NULL) t2
	ON t1.inventory_id = t2.inventory_id;

 

+ where절, order by

지점 1의 데이터

limit는 페이징을 위한 것

SELECT 
   t1.inventory_id inventoryId
   , t1.film_id filmId
   , t1.title title
   , t1.last_update lastUpdate
   , t2.customer_id customerId
   , IFNULL(t2.rental_date, '대여가능') rentalDate
FROM  
      (SELECT
      i.inventory_id
      , i. film_id
      , f.title
      , i.last_update
      , i.store_id
      FROM inventory i INNER JOIN film f
      ON i.film_id = f.film_id) t1
         LEFT OUTER JOIN 
         (SELECT inventory_id, customer_id, rental_date
         FROM rental
         WHERE return_date IS NULL) t2
         ON t1.inventory_id = t2.inventory_id
WHERE t1.store_id = 1
ORDER BY t1.inventory_id DESC
LIMIT 0, 10;