sql - How to get pairs from sqlite -

i database , playing sqlite3 group project.

i have 2 tables this:

table 1:     tv_show_id, tv_show_rating  table 2:     tv_show_id, cast_id 

each tv_show has 1 unique id, in table 2 there multiple cast_ids each tv_show

so have this:

table 1:  1234, 90 5678, 88  table 2: 1234, "person 1" 1234, "person 2" 5678, "person 1" 5678, "person 3" 

i want following results: (person a, person b, # of shows together)

(person 1, person 2, 1) (person 1, person 3, 1) (person 2, person 1, 1) (person 2, person 3, 0) (person 3, person 1, 1) (person 3, person 2, 0) 

how can use joins these results?

you can try this. fiddle

select z.id,count(w.show_id) (   select distinct concat(x.cid,',',x.did) id      (    select tt.cast_id cid,ttt.cast_id  did    t2 tt,t2 ttt    tt.cast_id <> ttt.cast_id    ) x   left join t2  on x.cid = t2.cast_id  ) z left join (select show_id, group_concat(cast_id order cast_id) cid            t2            group show_id            union             select show_id, group_concat(cast_id order cast_id desc) cid            t2            group show_id            ) w on z.id = w.cid group z.id;