Mysql - insert counting query into another query
In postgres I'm pretty sure you can do something like this
SELECT
authors.stage_name,
count(select id from books where books.author_id = authors.id)
FROM
authors,
books;
Essentially, in this example, I would like to return a list of authors and how many books have been written ... in the same query.
Is it possible? I suspect this approach is rather naive.
Thank:)
+1
source to share
2 answers
Well, first of all, it returns the Cartesian work of all authors to all books, regardless of whether or not that author wrote that book.
This is how I will write the query to get the result you want to say:
SELECT a.stage_name, COUNT(b.id)
FROM authors a
LEFT OUTER JOIN books b ON (a.id = b.author_id)
GROUP BY a.id;
You need to learn how to write join queries if you are using SQL. Joining SQL is what a loop is for application programming languages. That is, this is a fundamental programming construct that you need to know.
+1
source to share