More efficient way to write this query

[Biology Forums]

Here it is, it produces the information I want, but I'm wondering how to add a LEFT JOIN in the first mysql request, rather than separating it into 2:

Code Select Expand


//GET ALL THE ROWS IN THIS TABLE
$request = $smcFunc['db_query']('', '
SELECT t.ID, t.textbook, t.edition, t.author, t.img
FROM {db_prefix}textbook as t
ORDER BY ID DESC');

$context['textbooks'] = array();

while ($row = $smcFunc['db_fetch_assoc']($request))
{
// COUNT HOW MANY QUESTIONS FOR EACH TEXTBOOK ID
$dbrequest = $smcFunc['db_query']('', '
SELECT COUNT(ALL textbook_id) AS total
FROM {db_prefix}summary
WHERE textbook_id = {int:id}',
array(
'id' => $row['ID']
)
);
list ($summary['total']) = $smcFunc['db_fetch_row']($dbrequest);
$smcFunc['db_free_result']($dbrequest);

$context['textbooks'][] = array(
'ID' => $row['ID'],
'textbook' => $row['textbook'],
'edition' => ordinalize($row['edition']),
'author' => $row['author'],
'img' => $row['img'],
'count' => $summary['total']
);
}
$smcFunc['db_free_result']($request);

[albertlast]

how many rows you got?
what is the min/max textbook_id in summary?

[Biology Forums]

The number of rows I get equals the number of rows in "textbook", which is 17.

The textbook_id column in "summary" contains thousands of rows ranging from 1 to 17