Combine two queries into one.

[adam.adamski.96155]

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.

[pritaeas]

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

[adam.adamski.96155]

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.

[pritaeas]

Perhaps these help:

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
http://www.sitepoint.com/understanding-sql-joins-mysql-database/

[adam.adamski.96155]

I will spend some time with those links, thanks again :D

[Icone]

Do some practice on ms access query design, it might help you a lot.