MySQL A left join B on B.cid=A.id 左链接查询失败，求解

mysql> desc fb_category;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| upid  | int(10)     | NO   | MUL | NULL    |                |
| name  | varchar(20) | NO   |     | NULL    |                |
| sort  | int(4)      | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc fb_relation;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(10) | NO   | PRI | NULL    | auto_increment |
| siteid  | int(10) | NO   | MUL | NULL    |                |
| cid     | int(10) | NO   | MUL | NULL    |                |
| uptime  | int(10) | NO   |     | NULL    |                |
| hit     | int(10) | NO   |     | NULL    |                |
| hittime | int(10) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

对应关系

fb_category.id    =    fb_relation.cid
fb_site.id        =    fb_relation.siteid

实现需求

DROP VIEW IF EXISTS fb_v_category;
CREATE VIEW `fb_v_category` AS 
select c1.*,
count(c2.`id`) AS `subtotal`,
count(r.id) as sitetotal
from `fb_category` c1 
left join `fb_relation` r on r.cid=c1.id
left join `fb_category` c2 on c1.id=c2.upid
where c1.id=r.cid
group by c1.`id` 
order by c1.`sort` asc, c2.sort asc ,sitetotal desc;

测试语句：创建成功

drop view if exists fb_v_category;
create view fb_v_category as 
    select c1.*,count(r.cid) as sitetotal 
    from fb_category c1 
    left join fb_relation r on c1.id=r.cid
    group by c1.id 
    order by c1.sort asc 

测试语句：创建成功

--视图表创建正常
create view fb_v_category as
    select c1.*,count(c2.upid) as subtotal 
    from fb_category c1 
    left join fb_category c2 on c2.upid=c1.id
    group by c1.id
    order by c1.sort asc