php - 如何在MySQL中使用单个查询从另一个表中获取计数(无数据时为零)？

我有两张桌子-“提供的”和“联合的”。我需要从联接表中计算“提供的id”，如果找不到，则为零。我需要的表格和输出如下。如何在mysql中使用单个查询来实现这一点？

TABLE : OFFERED
===============
offered_id    data
-----------   ----
    1         aaaa
    2         bbbb
    3         cccc
    4         dddd
    5         eeee
    6         ffff

TABLE : JOINED
===============
joined_id      offered_id
-----------    ----------
    1               5
    2               2
    3               2
    4               1
    5               3
    6               2
    7               5

OUTPUT REQUIRED
===============
offered_id      data     count(offered_id) from joined table.(0 for no entry)
-----------    -----     ------------------------------------------------
    1           aaaa          1
    2           bbbb          3
    3           cccc          1
    4           dddd          0
    5           eeee          1
    6           ffff          0

最佳答案

使用子查询可以实现。。。

SELECT OFFERED.offered_id, OFFERED.data, (SELECT COUNT(JOINED.joined_id) FROM JOINED WHERE JOINED.offered_id = OFFERED.offered_id) AS count_joined FROM OFFERED