我有两个表broadcast
和contact
,我想在这个特定的广播收件人的数量计数,如果没有用户仍然所有广播列表应显示.为此,我通过这个左连接查询获得了所需的输出.
SELECT b.id, b.brl_name, COUNT(c.broadcast_list_id) AS Recepients FROM broadcastlistsms b LEFT JOIN addcontact c ON b.id = c.broadcast_list_id GROUP BY b.id,b.brl_name;
但我在两个表中都有两个公共字段,它们是由和isdeleted创建的,isdeleted(1)是删除特定的联系人和广播.所以我只想得到联系人数量,它不是由特定用户删除和创建的.与广播类似,广播不应由特定用户删除和创建.
我try 了这个,但它没有提供有0个联系人的广播条目.
SELECT b.id, b.brl_name, COUNT(c.broadcast_list_id) AS Recepients FROM broadcastlistsms b LEFT JOIN addcontact c ON b.id = c.broadcast_list_id
WHERE (b.createdby_id = 1 and b.isdeleted = 0) and (c.createdby_id = 1 and c.isdeleted = 0)
GROUP BY b.id,b.brl_name;
在future ,联系人表中还会有3个字段,如isdeleted,然后我还需要判断所有这3个字段,并根据该字段需要一个计数值.