SQLize | PHPize | SQLtest

-- create table CREATE TABLE IF NOT EXISTS QT_Films ( id int NOT NULL AUTO_INCREMENT, year varchar(10) NOT NULL, title varchar(200) NOT NULL, company varchar(200) NOT NULL, PRIMARY KEY (id) ) DEFAULT CHARSET = utf8; INSERT INTO QT_Films (year,title,company) VALUES ('1992','Reservoir Dogs','Miramax'), ('1994','Pulp Fiction','Miramax'), ('1997','Jackie Brown','Miramax'), ('2003','Kill Bill: Volume 1','Miramax'), ('2004','Kill Bill: Volume 2','Miramax'), ('2007','Death Proof','Dimension Films'), ('2009','Inglourious Basterds','The Weinstein Company / Universal Pictures'), ('2012','Django Unchained','The Weinstein Company / Sony Pictures Releasing'), ('2015','The Hateful Eight','The Weinstein Company'), ('2019','Once Upon a Time in Hollywood','Sony Pictures Releasing'), ('2022 - ?','The Final Quentin Tarantino Film','Unknown'); -- create initial CTE WITH QTF AS (SELECT * FROM QT_Films), -- remove The Weinstein Company RMV_TWC AS ( SELECT year, title, REPLACE((REPLACE(company,'The Weinstein Company',' ')),' / ','') AS company FROM QTF), -- Netflix acquired The H8ful Eight as a mini series H8FUL AS ( SELECT year, title, (CASE WHEN title LIKE '%Hateful%' THEN REPLACE(company,company,'Netflix') ELSE company END) AS company FROM RMV_TWC), -- group concat Kill Bill titles as QT considers KB Vol 1 & 2 as a single movie -- group by KB if title contains 'Kill Bill' & group by original title for others KILLBILL AS (SELECT (CASE WHEN max(title) LIKE '%Kill Bill%' THEN GROUP_CONCAT(year SEPARATOR '-') ELSE max(year) END) AS year, (CASE WHEN max(title) LIKE '%Kill Bill%' THEN GROUP_CONCAT(title SEPARATOR ', ') ELSE max(title) END) AS title, max(company) AS company FROM H8FUL GROUP BY (CASE WHEN title LIKE '%Kill Bill%' THEN 'KB' ELSE title END)), QTF_RENAMED AS (SELECT year AS 'Year_Released', title AS 'Film_Title', TRIM(BOTH ' ' FROM company) AS 'Distributor' FROM KILLBILL) -- rename columns -- select SELECT * FROM QTF_RENAMED ORDER BY Year_Released;