mysql - 如果通过左连接在其他表中不匹配，则选择值

Question

我在从数据库的两个表中获取值时遇到了一些问题。我的数据库中有两个表，第一个是内存，第二个是付款mem 存储用户的名称和drawid支付表存储用户的抽奖和分期付款

用户每月向我们付款。因此，如果抽奖 ID 为 1 的用户在 2 月向我们付款，则两个表中的值是mem drawid=1 and name = something付款提取 = 1 和分期付款 = 2

mem中的drawid等同于payment中的draw

因此表具有多对多关系。现在我需要找到所有在第 4 个月之前还没有支付过 1 期分期付款的成员(member)的名单。

我正在使用这个查询

 SELECT drawid,contact,dnd,mem.name, count(*) as numPayments FROM mem 
 LEFT JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
 drawid HAVING numPayments < 4

一切正常，没有问题，唯一的问题是我还需要显示用户已支付的分期付款，因此我需要从表付款中获取所有分期付款，然后通过 while 循环显示。

这个查询很完美，但它给了我重复的结果!!!

  SELECT drawid,contact,dnd,mem.name, count(*) as numPayments,NULL numPaidPayments ,NULL PAID_CONTACT,NULL NAME_PAID FROM mem 
LEFT JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
drawid HAVING numPayments < 4
UNION
SELECT NULL drawid,NULL contact, NULL dnd, NULL name,NULL numPayments,COUNT(*) as numPaidPayments ,contact PAID_CONTACT,mem.name NAME_PAID  FROM mem 
INNER JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
drawid HAVING numPaidPayments >= 4

Answer 1

    SELECT drawid,contact,dnd,mem.name, count(*) as numPayments,NULL numPaidPayments ,NULL PAID_CONTACT,NULL NAME_PAID FROM mem 
    LEFT JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
    drawid HAVING numPayments < 4
    UNION
    SELECT NULL drawid,NULL contact, NULL dnd, NULL name,NULL numPayments,COUNT(*) as numPaidPayments ,contact PAID_CONTACT,mem.name NAME_PAID  FROM mem 
    INNER JOIN payment ON (mem.drawid = payment.draw) GROUP BY 
    drawid HAVING numPaidPayments >= 4

尝试上面的查询。