mysql 2 queries into 1

Question

$sql = $empire -> query("select * from issue 
where disabled='0' order by issue desc");
    while($r=$empire->fetch($sql)){
        //get total
        $total = $empire -> gettotal("select count(*) as 
        total from sendbook where issueid='".$r["issue"]."'");

        $list .= 'id: '.$r['id'].' issue :'.$r['issue'].' total:'.$total.'';
    }
echo $list;

id:1 issue: 23 total:10
id:6 issue: 24 total:0
id:7 issue: 24 total:15

how to use 1 query to finish this work?

Think you!

Answer 1

You can try:

SELECT id, issue,(SELECT count(issueid) AS count FROM sendbook WHERE (issueid = issue)) AS total 
FROM issue WHERE disabled='0' 
GROUP BY issue 
ORDER BY issue DESC;

Answer 2

SELECT issue.*, COUNT(*) AS total
FROM issue AS i
LEFT JOIN sendbook AS s ON i.issue = s.issueid
WHERE i.disabled='0' 
GROUP BY i.issue
ORDER BY i.issue DESC

It will be something in these lines. Didn't test the code so you may have to adjust...