How to include a counting subquery inside a main MySQL query

[ethant]

I’m trying to figure out how to write a MySQL query that gets data from one table and also counts related records from another table at the same time.

Here’s what I want to do with my database:

SELECT 
  writers.pen_name,
  (SELECT COUNT(*) FROM novels WHERE novels.writer_id = writers.id) as book_count
FROM 
  writers;

Basically I need to get a list of all writers along with how many novels each one has published. I want this all in one query instead of running separate queries.

I think there might be a better way to do this with joins or something but I’m not sure about the syntax. Can someone help me understand the right approach for MySQL? I’ve been struggling with this for a while and would really appreciate some guidance on the best practice here.

[Alice45]

your query’s totally fine! it’s a correlated subquery and does exactly what you need. you could use a join instead: SELECT w.pen_name, COUNT(n.id) FROM writers w LEFT JOIN novels n ON w.id = n.writer_id GROUP BY w.id but honestly, your original version’s way more readable.