MySQL 联接返回多行

本文关键字：返回 MySQL | 更新日期: 2023-09-27 18:32:59

我有一个用于查询联系人表的MySQL语句。但是，当它们加载到我的DataGrid时，同一联系人有多个副本（表中只有 1 个）。

这让我相信我的MySQL陈述有问题。这就是我目前所拥有的;

myQuery.CommandText = @"SELECT contacts.contactID, contacts.companyID, 
companies.name, contacts.donestatus, employees.name,
people.ID, people.firstname, people.lastname, 
contacts.contractID, contacts.date, contacts.time, 
presets.presettext, contacts.madeby, contacts.description
FROM contacts
LEFT OUTER JOIN companies ON contacts.companyID = companies.ID
LEFT OUTER JOIN employees ON contacts.employeeID = employees.ID
LEFT OUTER JOIN people ON contacts.personID = people.ID
JOIN presets ON contacts.type = presets.presetIDFoxPro
WHERE contacts.companyid = @CompanyID
AND presets.presetreferencefoxpro = 8
ORDER BY contacts.date DESC";

我已经检查了该方法，它肯定只调用一次，所以对我来说它必须是语句。多个JOINS是否会导致为成功的每个JOIN加载Contact？

MySQL 联接返回多行

在这种情况下，这取决于您想要显示的内容。如果一个联系人可以有多个相关的公司、员工或人员，那么你将带回一条记录，其中包含它们与当前SQL的每一次组合。例如，如果一个联系人各有 2 行，那么 8 行（即 2 x 2 x 2）将返回该联系人。

如果您希望每个联系人的所有详细信息

都在一行上，则可以使用子查询来连接每个联系人的所有详细信息，然后将结果与联系人表联接。

举个简单的例子：-

SELECT contacts.contactID, contacts.companyID, 
        sub_companies.name, contacts.donestatus, sub_employees.name,
        sub_people.all_id, sub_people.all_firstname, sub_people.all_lastname, 
        contacts.contractID, contacts.date, contacts.time, 
        presets.presettext, contacts.madeby, contacts.description
FROM contacts
INNER JOIN presets ON contacts.type = presets.presetIDFoxPro
LEFT OUTER JOIN 
(
    SELECT ID, GROUP_CONCAT(name)
    FROM companies 
    GROUP BY ID
) sub_companies ON contacts.companyID = sub_companies.ID
LEFT OUTER JOIN 
(
    SELECT ID, GROUP_CONCAT(name)
    FROM employees 
    GROUP BY ID
) sub_employees ON contacts.employeeID = sub_employees.ID
LEFT OUTER JOIN 
(
    SELECT ID, GROUP_CONCAT(ID) AS all_id, GROUP_CONCAT(firstname) AS all_firstname, GROUP_CONCAT(lastname) AS all_lastname
    FROM people 
    GROUP BY ID
) sub_people ON contacts.personID = sub_people.ID
WHERE contacts.companyid = @CompanyID
AND presets.presetreferencefoxpro = 8
ORDER BY contacts.date DESC
相关文章：