Getting items from one table that don't have a corresponding foreign key in another table-CodePudding

Hi I am working on a react app that is on a ruby on rails server setup, that uses postgres.

I have two tables in the database, movies and genres. In the movie table, there is a field genre id. And in the genre table its ID is how it links to movie table's genre id. I am trying to find all genres that don't have a corresponding id listed in the movie table. I have tried two different ways, with SQL directly.

sql = "SELECT count(*) FROM genres
       LEFT JOIN movies
       ON genres.id = movies.genre_id
       WHERE genre.id = movies.genre"
        
genres_with_no_movie = ActiveRecord::Base.connection.exec_query(sql).rows[0][0]

The ruby on rails way.

genres_with_no_movie = Genre.joins(:movies).count()

Both of these give me the number of connections. It gives me places where a movie does have a corresponding genre. I am trying to find genres that don't have a movie that connects. I tried WHERE NOT EXISTS in place of where and that did not work.

Any help would be greatly appreciated.

CodePudding user response：

LEFT JOIN allows you to retrieve the rows from the table genres and which have no correspondance in movies, ie movies.genre IS NULL :

SELECT count(*) 
FROM genres
LEFT JOIN movies
ON movies.genre_id = genres.id
WHERE movies.genre IS NULL

CodePudding user response：

An alternative perhaps quicker, and (imho more direct) uses NOT EXISTS instead of join.

select count(*) 
  from genres g
 where not exists 
       (select null 
          from movies m 
         where m.genre_id = g.id
       );