Is RIGHT JOIN the way to go with this query?

Question

I have table in a PostgreSQL 9.2.24 database:

CREATE TABLE episodes_list (
  mid      serial PRIMARY KEY  -- movie id
, title    text NOT NULL
, is_movie integer NOT NULL    -- 1 for movie, 0 for episode
);

I need to display on the left titles of episodes, on the right titles of all movies (even those which don't have episodes).

103 | Microcosmos (high quality)                    |        1
103 | Snails' sexual act                            |        0
103 | Landing                                       |        0

Here are a few records. So eg. 'Microcosmos' should be on the right twice, with snail's sex act and landing on the left. That's how it should look like:

 Landing                                       | Microcosmos (high quality)
 Snails' sexual act                            | Microcosmos (high quality)

I wrote something like this:

select e1.title, e2.title from episodes_list e1 right join episodes_list e2 on e1.is_movie = 0 and e2.is_movie = 1 and e1.mid = e2.mid;

But it still shows on the right titles of episodes, rather then only movie titles (so I'm getting additional lines which I shouldn't be getting). So it looks like this: But it looks like this:

 Landing                                       | Microcosmos (high quality)
 Snails' sexual act                            | Microcosmos (high quality)
                                               | Snails' sexual act

What am I doing wrong here and how the query should look like?

That's very old and unsupported by now. [Urgently consider upgrading to a current version.](https://www.postgresql.org/support/versioning/) — Erwin Brandstetter, Dec 17 '18 at 17:35
I wound, but that's task in my university, and I'm stuck with this version — ItsNotFunny, Dec 17 '18 at 17:38
Not exactly flattering for your university, but makes no difference for this query. — Erwin Brandstetter, Dec 17 '18 at 17:41
I went ahead and added a table definition based on guesswork. Please [edit] if didn't get it right. — Erwin Brandstetter, Dec 17 '18 at 17:49
it might make more sense to convert `is_movie integer NOT NULL` into a `episode_type_id` and add a table `episode_type` where is store the types `movies` and `episode` that way you can more easy add `short movie` type for example in the future. — Raymond Nijland, Dec 17 '18 at 17:58

Erwin Brandstetter · Accepted Answer · 2018-12-17T18:11:38.183

This could be your query:

SELECT epi.title AS episode, mov.title AS movie
FROM        episodes_list epi
RIGHT  JOIN episodes_list mov ON epi.is_movie = 0
                             AND mov.mid = epi.mid
WHERE  mov.is_movie = 1;

This return all movies to the right, extended with episodes to the left where available.

is_movie should really be a boolean flag instead. integer is not the right type.

The most important bit to take away from this: format your queries (in any consistent fashion, not necessarily like mine) and use descriptive aliases when things get complicated.

The logic problem you had: conditions in the WHERE clause are equivalent to conditions in an INNER JOIN clause, but not to conditions in an OUTER JOIN.(LEFT JOIN / RIGHT JOIN is short for LEFT OUTER JOIN / RIGHT OUTER JOIN.)
See:

GMB · Answer 2 · 2018-12-17T17:38:25.017

1

You need a self LEFT JOIN on the table that selects episode records, with a WHERE clause that filters on movies only.

The below query should get the job done :

select 
    e2.title, 
    e1.title 
from episodes_list e1 
left join episodes_list e2 
    on e2.is_movie = 0
    and e1.mid = e2.mid
where e1.is_movie = 1

e1 represents movie records. e2 represents episodes records. The left join allows movies that have no episodes to be taken into account.

edited Dec 17 '18 at 17:38

answered Dec 17 '18 at 17:31

GMB

216,147
25
84
135

That works perfectly, but why doesn't the 'on e1.is_movie = 1' filter those records out and i have to add the 'where e1.is_movie = 1'? – ItsNotFunny Dec 17 '18 at 17:55
The LEFT JOIN is an optional join, meaning that even if there is no episode the query will still return the movie. You don’t want the condition that filters on movies to be optional, you need it to be mandatory. Else, you will end up with episodes being displayed in the first column (with second column empty) – GMB Dec 17 '18 at 18:28

Is RIGHT JOIN the way to go with this query?

2 Answers2