Selecting record that fulfills a condition but don’t want to consider the condition, need to consider the other records thats being matched

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
  
);

​x
 
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);

 
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'

 
select PROGRAM_FULL_TITLEfrom TABLE_Awhere 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!

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'

 
select PROGRAM_FULL_TITLEfrom TABLE_Awhere 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'​

Advertisement

Answer