CODEWITHSUNDEEP
sqlpostgresqljoin
ItsNotFunny
ItsNotFunny

Reputation: 63

Is RIGHT JOIN the way to go with this query?

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?

Upvotes: 1

Views: 45

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658292

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:

Upvotes: 2

GMB
GMB

Reputation: 222622

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.

Upvotes: 1

Related Questions