CODEWITHSUNDEEP
sqlpostgresql
AAA
AAA

Reputation: 2032

Count how many items in a one-to-many relationship

I have a table like this:

books(id, name, category) book_pages(id, book_id)

I want to get the number of books that have a book_page associated to it. (There could be books without book pages).

My first instinct was this but it returns many rows.

SELECT count(*) as count_table 
FROM books b
GROUP BY b.id 
HAVING ( (SELECT count(*) FROM book_pages bp WHERE bp.book_id = b.id) > 0 )

Sorry, I'm sure it's out there on Google somewhere but didn't know where to look.

Upvotes: 1

Views: 146

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I would simply use exists:

select count(*)
from books b
where exists (select 1 from book_pages bp where bp.book_id = b.id);

Upvotes: 1

Related Questions