SQL query to get a list of all elements belonging to a group that every element belongs to

Question

The intention is to be able to show all episodes for any show that a character has been a part of whether or not that character was in the episode itself.

I have the following tables...

Character

id	name
1	brain
2	pinky
3	dot

Show

id	name
1	Pinky & The Brain
2	Animaniacs

Profile

id	character	show
1	1	1
1	2	1
1	3	2

Episode

id	character	name	is_shareable
1	1	Clocktower	True
2	2	Escape	False
3	3	Return of the Jedi	True

I am trying to produce a query result that looks like this by selecting all characters in the same show for every character and the associated show

CharacterEpisode

user	show	episode
1	1	1
1	1	2
2	1	1
2	1	2
3	3	3

I can do something like:

CREATE OR REPLACE VIEW UserEpisode AS
SELECT
       c.id as character_id,
       s.id as show_id,
       e.id as episode_id
from character as c
     join profile as p on p.character_id = c.id
     join show as s on p.show_id = s.id
     join show as qs on p.show_id = qs.id
     join profile as qp on qp.show_id = qs.id
     join episode as e on qp.character_id = e.character_id;

1. Is there a better way to do this?

2. How would I modify this to include the episode.is_shareable column so that the view reflects directly associated is-shareable = False episodes and all is_shareable = True episodes that have any of the characters in the show for output that looks like:

user	show	episode
1	1	1
2	1	1
2	1	2
3	3	3

Answer 1

To get only the ids,. you don't need neither charater nor show

Only when you want the na,es you need to join them once

you can exclude the not sharacle, simple by adding a WHERE clause

CREATE OR REPLACE VIEW UserEpisode AS
SELECT
       p.character as character_id,
       p.show as show_id,
       e.id as episode_id
from      profile as p 
     join episode as e on p.character_id = e.character_id;
WHERE e.is_shareable = 'True'

eveb whewnb you want the shows name an characternames, you would make it simpler

CREATE OR REPLACE VIEW UserEpisode AS
SELECT
       p.character as character_id,
       p.show as show_id,
       e.id as episode_id
from profile as p 
     join show as s on p.show_id = s.id
     join character as c ON p.character_id = c.id
     join episode as e on p.character_id = e.character_id;
WHERE e.is_shareable = 'True'

If you want to see all charcters you can make such a query

CREATE OR REPLACE VIEW UserEpisode AS
SELECT
       p.character as character_id,
       p.show as show_id,
       e.id as episode_id
from character as c
     LEFT JOIN profile as p ON p.character_id = c.id
     LEFT join show as s on p.show_id = s.id
     LEFT join episode as e on p.character_id = e.character_id;
WHERE e.is_shareable = 'True' OR e.is_shareable IS NULL