Join two tables by MAX number

Question

I have two table like this;

|episodes
|-----------|------|------|-------|-------|
|id|movie_id|title |season|episode|scraped|
|-----------|-------------|---------------|
|1 |22      |ep1   |5     |1      |0      |
|2 |22      |ep2   |6     |1      |0      |
|3 |33      |ep1   |7     |1      |0      |
|4 |33      |ep2   |7     |2      |0      |
|-----------------------------------------|


|pages                                                    
|----------------|------------|
|pid |  imdb_id  |  imdb_title|
|----------------|-------------
|11  |  X-Files  |  imdb1     |
|22  |  Seinfeld |  imdb2     |
|33  |  Lost     |  imdb3     |
|-----------------------------|          

And I want to combine two tables to one according to the only MAX number of seasons. Example; Seinfeld's last season is 6 and Lost's only season is 7.

Table final version should be the like this.

|-----------|-----|------|-------|-------|---|--------|----------|
|id|movie_id|title|season|episode|scraped|pid| imdb_id|imdb_title|
|-----------|-----|------|---------------|---|--------|----------|
|3 |33      |ep1  |7     |1      |0      |11 |Lost    |imdb3     |
|4 |33      |ep2  |7     |2      |0      |11 |Lost    |imdb3     |
|2 |22      |ep2  |6     |1      |0      |22 |Seinfeld|imdb2     |
|----------------------------------------------------------------|

I tried this query, but can't include pages table.

SELECT a.*
FROM episodes a
INNER JOIN (
    SELECT movie_id, MAX(season) season
    FROM episodes
    GROUP BY movie_id
) b ON a.movie_id = b.movie_id AND a.season = b.season
WHERE a.scraped = '0'

Later tried another query this time added pages table but this query very very slow than before.

SELECT a.*, c.pid AS page_id, c.imdb_id, c.imdb_title 
 FROM episodes a    
 INNER JOIN pages AS c ON c.id = a.movie_id                                        
 LEFT OUTER JOIN episodes b ON a.movie_id = b.movie_id AND a.season < b.season                                        
 WHERE b.movie_id IS NULL and a.scraped = '0' 

I know, a bit confused, but I hope explain it correctly. How can I solve this problem?

Answer 1

You should join witn the max(season) group by movie_id

select e.id, e.movie_id, e.title, e.season, e,epison, p.pid, p.imdb_id, b.imdb_title
from episod 2 
inner join pages p on p.pid = e.movie_id
inner join (
  select movie_di, max(season) as max_season
  from episodes 
  group by movie_id
) t on t.movie_id = e.movie_id and t.max_season = e.season