Filter one table based on another in Postgres

Question

I have two tables with the following columns:

Authors

id | name | age

Books

authorId | title | pages

I need to somehow get top five of the author names who have books with more than 200 pages. I experimented with some types of joins and subqueries and this was my best shot:

SELECT 
    author.name, 
    (SELECT COUNT(*) FROM books WHERE pages > 200 AND 'books.authorId' = 'author.id') AS PageCount 
FROM 
    authors AS author 
ORDER BY 
    PageCount DESC

Unfortunately, it returns PageCount as 0.

What would be the way to get the needed result?

ScaisEdge · Accepted Answer · 2019-07-27 19:04:29Z

2

You could use an inner join, count, and group by instead of a subquery:

    SELECT  au.name, COUNT(*) PageCount
    FROM    authors AS au
    INNER JOIN books ON  "books"."authorId" = au.id 
    WHERE  books.pages > 200 
    GROUP BY au.name
    ORDER BY PageCount DESC

Don't use single quotes around column names. If you need to, use backticks instead. Single quotes are for literal strings, not for column names.

edited Jul 27, 2019 at 19:04

answered Jul 27, 2019 at 16:06

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Doesntmeananything Over a year ago

Thanks! But I can't seem to confirm this is working because my select query fails with column books.authorId does not exist. My author.id is set as a primary key and books.authorId is a foreign key. Both of these are of type UUID. Is that the reason?

ScaisEdge Over a year ago

no .. books.authorId .. is mentioned in your table schema .. so be sure that the column name you have in you query match really your columns name ..

Doesntmeananything Over a year ago

I know it sounds strange, but that's exactly what I'm doing. Here's my query in a Heroku Postgres instance. Backticks don't help either. Feels like something's wrong with my models.

ScaisEdge Over a year ago

try use quote around table.column as "books.authorId" and be sure for right capitalized characters matching

Doesntmeananything Over a year ago

Yeah, I've tried that as well, but it gives me pretty much the same error.

|

Collectives™ on Stack Overflow

Filter one table based on another in Postgres

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related