MySql表如何获取所有联系人？

Question

我有一个 MySQL 查询(我使用 phpmyadmin)，并且我知道不应该像这样创建表，但我所从事的项目必须只有一个表。这是我的sql查询。

 CREATE VIEW v_sugarcrm AS 
SELECT AC.id, A.id AS account_id, A.name, A.description, A.industry, A.phone_fax, A.phone_office, A.shipping_address_street, A.shipping_address_city, A.shipping_address_state, A.shipping_address_postalcode, A.shipping_address_country, C.id AS contact_id, C.first_name, SUBSTRING_INDEX(C.last_name, ' ', -1) as last_name,concat(c.first_name, ' ', c.last_name) as full_name, SUBSTRING_INDEX(SUBSTRING_INDEX(concat(c.first_name, ' ', c.last_name), ' ', 2), ' ', -1) as middle_name, C.title, C.department, C.phone_home, C.phone_mobile, C.phone_work, C.primary_address_street, C.primary_address_city, C.primary_address_state, C.primary_address_postalcode, C.primary_address_country, C.deleted, EA.email_address as email_account,
   SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(EA_C1.email_address), ',', 1), ',', -1) as email_2,
   If(length(GROUP_CONCAT(EA_C1.email_address)) - length(replace(GROUP_CONCAT(EA_C1.email_address), ',', ''))>=1,
   SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(EA_C1.email_address), ',', 2), ',', -1), NULL) as email_3,
   If(length(GROUP_CONCAT(EA_C1.email_address)) - length(replace(GROUP_CONCAT(EA_C1.email_address), ',', ''))>=2,
   SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(EA_C1.email_address), ',', 3), ',', -1), NULL) as email_4,
   If(length(GROUP_CONCAT(EA_C1.email_address)) - length(replace(GROUP_CONCAT(EA_C1.email_address), ',', ''))>=3,
   SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(EA_C1.email_address), ',', 4), ',', -1), NULL) as email_5,
EA_C.email_address as email_contact,
GROUP_CONCAT(EA_C1.email_address) as alt_email_contact
FROM ACCOUNTS A 
INNER JOIN ACCOUNTS_CONTACTS AS AC ON A.id = AC.account_id 
INNER JOIN CONTACTS AS C ON C.id = AC.contact_id 
LEFT JOIN EMAIL_ADDR_BEAN_REL AS ER ON ER.bean_id = A.id 
AND ER.bean_module = "Accounts" AND ER.primary_address =1 
LEFT JOIN EMAIL_ADDR_BEAN_REL AS ER_C ON ER_C.bean_id = C.id 
AND ER_C.bean_module = "Contacts" AND ER_C.primary_address =  1
LEFT JOIN EMAIL_ADDR_BEAN_REL AS ER_C1 ON ER_C1.bean_id = C.id and ER_C1.deleted !=1
AND ER_C1.primary_address !=1
LEFT JOIN EMAIL_ADDR_BEAN_REL AS ER_A1 ON ER_A1.bean_id = A.id AND ER_A1.deleted !=1 
and ER_A1.primary_address !=1 
LEFT JOIN EMAIL_ADDRESSES AS EA ON ER.email_address_id = EA.id
LEFT JOIN EMAIL_ADDRESSES AS EA_C ON ER_C.email_address_id = EA_C.id
LEFT JOIN EMAIL_ADDRESSES AS EA_C1 ON ER_C1.email_address_id = EA_C1.id
AND EA_C1.invalid_email !=1 AND EA_C1.opt_out !=1
LEFT JOIN EMAIL_ADDRESSES AS EA_A1 ON ER_A1.email_address_id = EA_A1.id 
AND EA_A1.invalid_email !=1 AND EA_A1.opt_out !=1
GROUP BY A.ID

Account_Contacts 表 http://i.stack.imgur.com/KUECA.png

我的主要问题围绕查询顶部的两个内部联接，截至目前，此查询为我提供了联系人和公司所需的所有信息，但是当有多个联系人与一家公司仅显示一个联系人，而不是两者。我想知道如何更改我的查询，以便它显示所有联系人，即使公司的信息相同，联系人的信息应该不同，并且他们也应该有不同的 ID。

我真的需要帮助!

Answer 1

我并不肯定，但我认为您可能可以通过更多方式简化查询(消除 EMAIL_ADDR_BEAN_REL 和 EMAIL_ADDRESSES 的多次使用)连接到 EMAIL_ADDR_BEAN_REL 的复杂连接条件。

与下面的片段类似的东西。我稍微改变了逻辑，猜测一些可能的错误或不必要的条件； GUID 使用提示您可能不需要知道“bean 模块”:

...
FROM ACCOUNTS A 
   INNER JOIN ACCOUNTS_CONTACTS AS AC ON A.id = AC.account_id 
   INNER JOIN CONTACTS AS C ON C.id = AC.contact_id 
   LEFT JOIN EMAIL_ADDR_BEAN_REL AS ER 
       ON ER.bean_id IN (A.id, C.id) 
       AND (ER.primary_address = 1 OR ER.deleted != 1)
   LEFT JOIN EMAIL_ADDRESSES AS EA ON ER.email_address_id = EA.id
...

我怀疑它也可能会简化您在 SELECT 子句中所做的所有事情。