时间戳

首页 > 解决方案 > 同一表上的多个连接，如果连接字段为 NULL，则不返回结果

sql - 同一表上的多个连接，如果连接字段为 NULL，则不返回结果

问题描述

SELECT organizations_organization.code as organization,
core_user.email as Created_By,
assinees.email as Assigned_To,
from tickets_ticket 
JOIN organizations_organization on tickets_ticket.organization_id = organizations_organization.id
JOIN core_user on tickets_ticket.created_by_id  = core_user.id 
Left JOIN core_user as assinees on assinees.id = tickets_ticket.currently_assigned_to_id

在上面的查询中，如果 ticket_ticket.currently_assigned_to_id 为 null，则不返回 ticket_ticket 中的该行

> Records In tickets_ticket = 109
> Returned Records = 4 (out of 109 4 row has value for currently_assigned_to_id  rest 105 are null )
> Expected Records = 109 (with nulll set for  Assigned_To)

注意我正在尝试在同一个表上实现多个连接

标签： sqlpostgresqljoin

解决方案

LEFT JOIN 不能杀死输出记录，你的问题在这里：

JOIN core_user on tickets_ticket.created_by_id = core_user.id

此连接会杀死不匹配的记录

LEFT JOIN core_user on tickets_ticket.created_by_id = core_user.id

推荐阅读