mysql - 从多个表中选择并左连接到一个表

Question

谁能帮我解决这个问题？

当我只加入 2 个表时。效果很好。

但是当我尝试从多个表中进行选择时。它不工作。这是无效代码的示例

SELECT ca.cdate as tarikh, sum(transaction.amount) as totalamount 
FROM agent ag, town tw, calendar ca 
LEFT JOIN transaction ON ca.cdate =  DATE_FORMAT(transaction.created_at, '%Y-%m-%d') 
WHERE ca.cdate >= '2014-06-01' AND ca.cdate <= '2014-06-11' 
AND transaction.agent_id = ag.id 
AND ag.town_id = tw.id 
AND tw.state_id = 7;

结果是

添加分组后

SELECT ca.cdate as tarikh, sum(transaction.amount) as totalamount 
FROM agent ag, town tw, calendar ca 
LEFT JOIN transaction ON ca.cdate =  DATE_FORMAT(transaction.created_at, '%Y-%m-%d') 
WHERE ca.cdate >= '2014-06-01' AND ca.cdate <= '2014-06-11' 
AND transaction.agent_id = ag.id 
AND ag.town_id = tw.id 
AND tw.state_id = 7
GROUP BY
ca.cdate;

尝试 Ambrish 建议的查询

SELECT ca.cdate as tarikh, sum(transaction.amount) as totalamount 
FROM calendar ca 
LEFT JOIN transaction ON ca.cdate = DATE_FORMAT(transaction.created_at, '%Y-%m-%d') 
LEFT JOIN agent ag on transaction.agent_id = ag.id 
LEFT JOIN town tw on ag.town_id = tw.id 
WHERE ca.cdate >= '2014-06-01' 
AND ca.cdate <= '2014-06-11'
AND tw.state_id = 7;

结果是

安布里什第二次

SELECT ca.cdate as tarikh, sum(transaction.amount) as totalamount 
FROM calendar ca 
LEFT JOIN transaction ON ca.cdate =  DATE_FORMAT(transaction.created_at, '%Y-%m-%d') 
LEFT JOIN agent ag on transaction.agent_id = ag.id
LEFT JOIN town tw on ag.town_id = tw.id 
WHERE ca.cdate >= '2014-06-01' AND ca.cdate <= '2014-06-11' 
AND tw.state_id = 7
group by ca.cdate;

结果是

Answer 1

SELECT ca.cdate as tarikh, sum(transaction.amount) as totalamount 
FROM calendar ca 
LEFT JOIN transaction ON ca.cdate =  DATE_FORMAT(transaction.created_at, '%Y-%m-%d') 
LEFT JOIN agent ag on transaction.agent_id = ag.id
LEFT JOIN town tw ag.town_id = tw.id 
WHERE ca.cdate >= '2014-06-01' AND ca.cdate <= '2014-06-11' 
AND tw.state_id = 7
group by ca.cdate;

如果没有返回结果，则可能没有匹配的记录。因此，请尝试不使用 WHERE 子句(或子集)的查询。或者具有不同值的查询。