im practicing with SQL and have created this table in MySQL:
CREATE TABLE TABLE_A ( PROGRAM_ID varchar (265) , SERIES_ID varchar (265) , SEASON_ID varchar (265) , ACCOUNT_ID varchar (265) , START_DATE_EST date, END_DATE_EST date, ACCOUNT_HOME_COUNTRY varchar (265) , PROGRAM_FULL_TITLE varchar (265) , SEASON_FULL_TITLE varchar (265) , SERIES_FULL_TITLE varchar (265) , PROGRAM_TYPE varchar (265) , SEASON_NUMBER int, EPISODE_SEQUENCE_NUMBER int, EPISODE_SERIES_SEQUENCE_NUMBER int, RUNTIME bigint, WATCH_LENGTH_MS bigint, IS_STREAM int, IS_COMPLETED_STREAM int );
with this insert data
INSERT INTO TABLE_A VALUES ('fre123-34534', 'jlk456-nm-56', 'tre-765-fd', 'jewq345-b3', '2021-01-01','2021-01-01', 'MX', 'Chapter 9: The Marshall', 'The Mandalorian Season 2', 'The Mandalorian', 'episode', 2, 1, 9, 3244, 167251, 1, 0);
INSERT INTO TABLE_A VALUES ('qwe-567-dsa', 'jlk456-nm-56', 'hjfg-123-re', 'asd-345-h', '2021-06-06', '2021-06-06', 'BR', 'Chapter 1: The Mandalorian', 'The Mandalorian Season 1', 'The Mandalorian', 'episode', 1, 1, 1, 2377, 30005, 1, 0);
INSERT INTO TABLE_A VALUES ('mrvl-456-tre', 'null', 'null', 'asd-345-h', '2021-06-15', '2021-06-15', 'MX', 'Ice Age: Dawn Of The Dinosaurs', 'null', 'null', 'movie',0 ,0 ,0 , 5797, 4620000, 1, 1);
INSERT INTO TABLE_A VALUES ('2b81a' ,'null' ,'null' ,'43116' ,'2021-06-16' ,'2021-06-16' ,'BR' ,'Snow White and The Seven Dwarfs' ,'null' ,'null' ,'movie' ,0 ,0 ,0 ,5077 ,0 ,0 ,0);
INSERT INTO TABLE_A VALUES ('8a0f2' ,'886E-' ,'51B3-' ,'41d0c' ,'2021-06-11' ,'2021-06-11' ,'MX' ,'Alexs Choice' ,'Wizards of Waverly Place' ,'Wizards of Waverly Place' ,'episode' ,1 ,7 ,7 ,1443 ,922667 ,1 ,1);
INSERT INTO TABLE_A VALUES ('ae5e7' ,'468B-' ,'6173-' ,'add25' ,'2021-07-02' ,'2021-07-02' ,'BR' ,'Hawaii Pug-Oh / A.R.F.' ,'Puppy Dog Pals' ,'Puppy Dog Pals' ,'episode' ,1 ,1 ,1 ,1525 ,1420719 ,1 ,1);
INSERT INTO TABLE_A VALUES ('ae5e7' ,'468B-' ,'6173-' ,'add25' ,'2021-07-02' ,'2021-07-02' ,'BR' ,'Hawaii Pug-Oh / A.R.F.' ,'Puppy Dog Pals' ,'Puppy Dog Pals' ,'episode' ,1 ,1 ,1 ,1525 ,1420719 ,1 ,1);
INSERT INTO TABLE_A VALUES ('04ff0' ,'6861-' ,'780B-' ,'df57d' ,'2021-06-28' ,'2021-06-28' ,'MX' ,'Lamentis' ,'Loki Season 1' ,'Loki' ,'episode' ,1 ,3 ,3 ,2543 ,1952888 ,1 ,0);
INSERT INTO TABLE_A VALUES ('77c78' ,'A9C2-' ,'5006-' ,'72d4c' ,'2021-07-02' ,'2021-07-02' ,'MX' ,'Sleepwalkin' ,'Mickey Mouse (Shorts)' ,'Mickey Mouse (Shorts)' ,'episode' ,1 ,14 ,14 ,321 ,226605 ,1 ,1);
INSERT INTO TABLE_A VALUES ('eabca' ,'B26A-' ,'65AC-' ,'59548' ,'2021-06-23' ,'2021-06-23' ,'BR' ,'EPISODE 219' ,'ONCE (YR 3 2018/19 EPS 161-220)' ,'O11CE' ,'episode' ,3 ,59 ,219 ,1376 ,981426 ,1 ,1);
INSERT INTO TABLE_A VALUES ('dbe51' ,'null' ,'null' ,'7414f' ,'2021-06-20' ,'2021-06-20' ,'BR' ,'Luca' ,'null' ,'null' ,'movie' ,0 ,0 ,0 ,6062 ,450083 ,1 ,0);
INSERT INTO TABLE_A VALUES ('78356' ,'A307-' ,'F03B-' ,'071fa' ,'2021-06-27' ,'2021-06-27' ,'MX' ,'Point of No Return' ,'Star Wars: The Clone Wars' ,'Star Wars: The Clone Wars' ,'episode' ,5 ,13 ,101 ,1537 ,1323790 ,1 ,1);
INSERT INTO TABLE_A VALUES ('04ff0' ,'6861-' ,'780B-' ,'107e4' ,'2021-06-23' ,'2021-06-23' ,'AR' ,'Lamentis' ,'Loki Season 1' ,'Loki' ,'episode' ,1 ,3 ,3 ,2543 ,1891918 ,1 ,0);
INSERT INTO TABLE_A VALUES ('d657a' ,'3D23-' ,'EBED-' ,'cdb2c' ,'2021-06-25' ,'2021-06-25' ,'AR' ,'outfoX' ,'The Gifted' ,'The Gifted' ,'episode' ,1 ,9 ,9 ,2670 ,2564518 ,1 ,1);
INSERT INTO TABLE_A VALUES ('dbe51' ,'null' ,'null' ,'64fda' ,'2021-06-22' ,'2021-06-22' ,'MX' ,'Luca' ,'null' ,'null' ,'movie' ,0 ,0 ,0 ,6062 ,5448827 ,1 ,1);
INSERT INTO TABLE_A VALUES ('b5e6b' ,'CC3F-' ,'94B7-' ,'5216c' ,'2021-06-16' ,'2021-06-16' ,'MX' ,'Band-A-Rooney' ,'Liv and Maddie' ,'Liv and Maddie' ,'episode' ,2 ,19 ,40 ,1485 ,680495 ,1 ,1);
INSERT INTO TABLE_A VALUES ('d500' ,'a600' ,'33sc-' ,'6ab3c' ,'2021-06-20' ,'2021-06-20' ,'AR' ,'Avengers End Game' ,'null' ,'null' ,'movie' ,1 ,2 ,2 ,3269 ,1605864 ,1 ,0);
INSERT INTO TABLE_A VALUES ('d4081' ,'6861-' ,'780B-' ,'6ab3c' ,'2021-06-20' ,'2021-06-20' ,'AR' ,'The Variant' ,'Loki Season 1' ,'Loki' ,'episode' ,1 ,2 ,2 ,3269 ,1605864 ,1 ,0);
how i can check what other content(PROGRAM_FULL_TITLE) watched those that streamed a program, for example, Loki? based on the 2 last insert(ACCOUNT_ID = ‘6ab3c’) in this case, i mean, i wanna show ‘Avengers End Game’ and not
i was trying something like this:
select PROGRAM_FULL_TITLE from TABLE_A where PROGRAM_FULL_TITLE in (select * from TABLE_A where IS_STREAM = 1 and SERIES_FULL_TITLE='Loki') and SERIES_FULL_TITLE <> 'Loki'
i search over here but couldnt solve it, please if someone can help me with this. Thanks in advance!
Advertisement
Answer
You have incorrect subquery.
select PROGRAM_FULL_TITLE from TABLE_A where PROGRAM_FULL_TITLE in (select PROGRAM_FULL_TITLE from TABLE_A where IS_STREAM = 1 and SERIES_FULL_TITLE='Loki' order by ACCOUNT_ID DESC LIMIT 2) and SERIES_FULL_TITLE <> 'Loki'