0

I have two tables, author and quote:
author: AID, Name, YOB, YOD.
quote: ID, AuthID, Content.
I currently get all the authors with one query and as I loop through them, I run a second query to get the count of quotes that have the current AID:

$sql = "SELECT AID, Name FROM author order by Name";
        if (!$res = $this->conn->query($sql)){
            echo $this->conn->error; die();
        } else {
            while($row = $res->fetch_assoc()){
                $count_sql = "select count(*) as count from quote where AuthID='".$row['AID']."'";
                if (!$count_res = $this->conn->query($count_sql)){
                echo $this->conn->error; die();
                } else {
                    $count_row = $count_res->fetch_assoc();
                    $row['count'] = $count_row['count'];
                }

Is it possible to collect the counts in a single query?
"SELECT AID, Name from author and count(AuthID=AID from quotes)"
I have seen talk of 'HAVING', 'IN' and other keywords, but can't find a comprehensible answer.

Edited by adam.adamski.96155

3
Contributors
5
Replies
6
Views
5 Years
Discussion Span
Last Post by Icone
1

Something like this:

SELECT a.AID, a.Name, COUNT(*) AS QuoteCount
FROM author a
LEFT JOIN quote q ON a.AID = q .AuthID
GROUP BY a.AID
ORDER BY a.Name
0

It's perfect thank you Pritaeas. I find it so difficult to visualise the table joins and relationships, I really need to spend some time on it to get my head around it.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.