mariadb 具有零到多联接的非重复选择查询

我有下面的sql查询。查询已经工作了好几年没有contacttags连接，但现在我们需要添加contacttags连接，这是零到多。下面的查询没有contacttags连接将返回零结果。现在我们有contacttags连接，它返回一行所有空值。当一个连接可以有零到多个时，正确的查询方法是什么？

SELECT `contacts`.`id`, `contacts`.`nameFirst`, `contacts`.`nameLast`, `contacts`.`nameBusiness`,
`e1`.`nameShort` AS `ownerEmployee`, `c2`.`formattedName` AS `ownerEmployeeName`,
`e2`.`id` AS `employeeId`, `e2`.`nameShort` AS `employeeNameShort`,
`v1`.`id` AS `vendorId`,
GROUP_CONCAT(`ct`.`id`) AS `contactTags`
FROM `contacts`
# employee assigned to contact - one to one
LEFT JOIN `employees` AS `e1` ON `e1`.`id` = `contacts`.`ownerEmployeeId` AND `e1`.`tenantId` = `contacts`.`tenantId`
LEFT JOIN `contacts` AS `c2` ON `c2`.`id` = `e1`.`contactId` AND `c2`.`tenantId` = `e1`.`tenantId`
# if contact is employee - zero or one
LEFT JOIN `employees` AS `e2` ON `e2`.`tenantId` = `contacts`.`tenantId` AND `e2`.`dateDeleted` IS NULL AND `e2`.`contactId` = `contacts`.`id`
# if contact is vendor - zero or one
LEFT JOIN `vendors` AS `v1` ON `v1`.`tenantId` = `contacts`.`tenantId` AND `v1`.`dateDeleted` IS NULL AND `v1`.`contactId` = `contacts`.`id` AND `v1`.`vendorStatus` = "Active"
# tags assigned to contact, zero to many
LEFT JOIN `contacttags` AS `ct` ON `ct`.`tenantId` = `contacts`.`tenantId` AND `ct`.`dateDeleted` IS NULL AND `ct`.`contactId` = `contacts`.`id`
WHERE `contacts`.`tenantId` = 3 AND `contacts`.`dateDeleted` IS NULL
 AND `contacts`.`contactStatus` in ("Active","Working") AND `e1`.`employeeStatus` != "Active"
ORDER BY `contacts`.`formattedName` ASC;

字符串
此查询用于更改where子句以显示需要显示的内容的许多场景。通过添加contacttags join，我们需要包含通过分配的标记进行搜索的选项。下面是将where子句更改为通过标记进行过滤的相同查询。该查询应该返回零个结果，但它返回一个结果将全部为null值。

SELECT `contacts`.`id`, `contacts`.`nameFirst`, `contacts`.`nameLast`, `contacts`.`nameBusiness`,
`e1`.`nameShort` AS `ownerEmployee`, `c2`.`formattedName` AS `ownerEmployeeName`,
`e2`.`id` AS `employeeId`, `e2`.`nameShort` AS `employeeNameShort`,
`v1`.`id` AS `vendorId`,
GROUP_CONCAT(`ct`.`id`) AS `contactTags`
FROM `contacts`
# employee assigned to contact - one to one
LEFT JOIN `employees` AS `e1` ON `e1`.`id` = `contacts`.`ownerEmployeeId` AND `e1`.`tenantId` = `contacts`.`tenantId`
LEFT JOIN `contacts` AS `c2` ON `c2`.`id` = `e1`.`contactId` AND `c2`.`tenantId` = `e1`.`tenantId`
# if contact is employee - zero or one
LEFT JOIN `employees` AS `e2` ON `e2`.`tenantId` = `contacts`.`tenantId` AND `e2`.`dateDeleted` IS NULL AND `e2`.`contactId` = `contacts`.`id`
# if contact is vendor - zero or one
LEFT JOIN `vendors` AS `v1` ON `v1`.`tenantId` = `contacts`.`tenantId` AND `v1`.`dateDeleted` IS NULL AND `v1`.`contactId` = `contacts`.`id` AND `v1`.`vendorStatus` = "Active"
# tags assigned to contact, zero to many
LEFT JOIN `contacttags` AS `ct` ON `ct`.`tenantId` = `contacts`.`tenantId` AND `ct`.`dateDeleted` IS NULL AND `ct`.`contactId` = `contacts`.`id`
WHERE `contacts`.`tenantId` = 3 AND `contacts`.`dateDeleted` IS NULL
 AND `contacts`.`contactStatus` in ("Active","Working") AND `ct`.`tagId`=999
ORDER BY `contacts`.`formattedName` ASC;

型

如果你只是想使用你的“通用”查询，你可以在查询中添加过滤器，那么你可以避免直接连接contacttag表-而是在where子句中使用and exists predicate 。这样做的最大好处是它不会对现有的查询产生任何行倍增效应，例如：

SELECT
      contacts.id
    , contacts.nameFirst
    , contacts.nameLast
    , contacts.nameBusiness
    , e1.nameShort AS ownerEmployee
    , c2.formattedName AS ownerEmployeeName
    , e2.id AS employeeId
    , e2.nameShort AS employeeNameShort
    , v1.id AS vendorId
FROM contacts
LEFT JOIN employees AS e1 ON e1.id = contacts.ownerEmployeeId
    AND e1.tenantId = contacts.tenantId
LEFT JOIN contacts AS c2 ON c2.id = e1.contactId
    AND c2.tenantId = e1.tenantId
LEFT JOIN employees AS e2 ON e2.tenantId = contacts.tenantId
    AND e2.dateDeleted IS NULL
    AND e2.contactId = contacts.id
LEFT JOIN vendors AS v1 ON v1.tenantId = contacts.tenantId
    AND v1.dateDeleted IS NULL
    AND v1.contactId = contacts.id
    AND v1.vendorStatus = 'Active'
WHERE contacts.tenantId = 3
    AND contacts.dateDeleted IS NULL
    AND contacts.contactStatus IN ('Active', 'Working')
    -- filter by contacttag.tagid without any row multiplication
    AND EXISTS (
        SELECT NULL
        FROM contacttags AS ct
        WHERE ct.tenantId = contacts.tenantId
        AND ct.dateDeleted IS NULL
        AND ct.contactId = contacts.id
        AND ct.tagId = 999
        )
ORDER BY contacts.formattedName ASC;

字符串

nb对于exists (select ...)中的查询，你可以使用select null或select 1或select *，它是这个查询的where子句决定了true或false，所以这里的select子句并不重要。

mariadb 具有零到多联接的非重复选择查询

1条答案

相关问题

热门标签

最新问答