CODEWITHSUNDEEP
postgresqlfull-outer-join
Emieligeter
Emieligeter

Reputation: 1

SQL filter on conditions before full outer join

I am trying to create an sql query (postgresql) which does a full outer join between two tables. For demonstration purposes I will use two tables: movies and genre. Some movies do not have a genre assigned yet and some genres are not in the movie database

Movie table:

Movie Genre_id Length
Inception 01 120
The shining NULL 120
Die hard 01 90

Genre table:

name id
Thriller 01
Drama 02

What I want is a list of genres and the number of movies in that genre. But, I only want to see movies longer than 90 minutes. So my thought process is I need a full outer join and a filter:

SELECT
COALESCE(genre.name,'No genre'),
COUNT(movies.name)
FROM movies
FULL OUTER JOIN genre ON genre.id = movies.genre_id
WHERE movies.length > 90
GROUP BY genre.name

However, this query gives me this output:

coalesce count
No genre 1
Thriller 1

I know why it gives me this output, and that is because 'Drama' doesn't match to any of the movies so a filter applied to 'movies' will not return anything. I need to somehow filter the movies before applying the full outer join. I have found two solutions myself which are a CTE or an inner join with the filters:

SELECT
COALESCE(genre.name,'No genre'),
COUNT(movies.name)
FROM movies
INNER JOIN movies m2 ON movies.name = m2.name AND movies.length > 90
FULL OUTER JOIN genre ON genre.id = movies.genre_id
GROUP BY genre.name

My question is: is there another way to solve this, which preferably doesn't use a CTE or a 'hacky' solution as I used above? I also found a solution using a subquery here FULL OUTER JOIN value condition but Im not sure how to use this in my case

(SQL Fiddle: http://sqlfiddle.com/#!15/860151/3)

EDIT: I found a solution:

SELECT
COALESCE(genre.name,'No genre'),
COUNT(movies.name)
FROM (SELECT * FROM movies WHERE movies.length > 90) movies
FULL OUTER JOIN genre ON genre.id = movies.genre_id
GROUP BY genre.name

Upvotes: 0

Views: 282

Answers (1)

Umut TEKİN
Umut TEKİN

Reputation: 962

Try this:

SELECT
COALESCE(genre.name,'No genre'),
COUNT(movies.name)
FROM movies
FULL OUTER JOIN genre ON genre.id = movies.genre_id
WHERE movies.length > 90 or genre_id is null
GROUP BY genre.name;

You only need to include your NULL values on genre_id column in movie tables.

Upvotes: 0

Related Questions