sugarcrm简单查询无法正常工作 [英] sugarcrm simple query not working

问题描述

我正在尝试检索联系人所属的公司的名称.表account_contacts中存在关系，当我尝试调整查询时它会吠叫

I am trying to retrieve the name of the compnay a contact belongs to. the relation ships exists in the table account_contacts, how ever when I try to tun the query it barks

SELECT
    accounts.`name`,
    contacts.first_name
FROM
    contacts,
    accounts
INNER JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
AND accounts.id = accounts_contacts.account_id

我得到的错误是

[Err] 1054 - Unknown column 'contacts.id' in 'on clause'

更改后:

SELECT
accounts.`name`,
contacts.first_name,
accounts.id
FROM
    contacts
INNER JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
JOIN accounts ON accounts.id = accounts_contacts.account_id
WHERE first_name = 'shamraiz' 

您的查询返回2行，并包含我期望的结果.帐户ID不同.但是，我已重做的查询以您的方式再次实现，无法正常工作. accountid相同，但返回2行.

your query returns 2 rows with the results i expect. the account id are different. However the query I have redone again implementing it your way does not work. the accountid is the same, but returns 2 rows.

SELECT
    contacts.id AS CONTACTID,
    accounts.id AS ACCOUNTID,
    contacts.first_name,
    contacts.last_name,
    contacts.phone_work,
    contacts.phone_fax,
    contacts.department,
    contacts.title,
    contacts.description,
    contacts.salutation,
    email_addresses.email_address,
    contacts.deleted
FROM
    contacts
INNER JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
    JOIN accounts on accounts.id = accounts_contacts.account_id
INNER JOIN email_addr_bean_rel ON contacts.id = email_addr_bean_rel.bean_id
INNER JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id

where first_name = 'shamraiz'

下一个查询返回3行，但是前2个重复了

the next query returns 3 rows, but the top 2 are dupplicated

SELECT
    contacts.id AS CONTACTID,
    accounts.id AS ACCOUNTID,
    contacts.first_name,
    contacts.last_name,
    contacts.phone_work,
    contacts.phone_fax,
    contacts.department,
    contacts.title,
    contacts.description,
    contacts.salutation,
    email_addresses.email_address,
    contacts.deleted
FROM
    contacts
inner JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
    left JOIN accounts on accounts.id = accounts_contacts.account_id
left JOIN email_addr_bean_rel ON contacts.id = email_addr_bean_rel.bean_id
left JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id
where first_name = 'shamraiz'

来自联系人

SELECT * FROM sugarcrm . contacts where first_name = 'shamraiz'返回2行

SELECT * FROMsugarcrm.contactswhere first_name = 'shamraiz' returns 2 rows

来自account_contact关系

SELECT * FROM sugarcrm . accounts_contacts where contact_id = '17619b5e-db07-fa3b-6748-51a73ef38c5e'返回1行

SELECT * FROMsugarcrm.accounts_contactswhere contact_id = '17619b5e-db07-fa3b-6748-51a73ef38c5e' returns 1 row

SELECT * FROM sugarcrm . accounts_contacts where contact_id = '003b0000006ZMDXAA4'返回1行.

SELECT * FROMsugarcrm.accounts_contactswhere contact_id = '003b0000006ZMDXAA4' returns 1 row.

因此，最终查询应返回2个不同的行，因为它们是两个具有相同名称的联系人加入了2个不同的公司.

So the final query should return 2 different rows as they are two contacts with similar names joined to 2 different companies.

一个联系人可以属于1家公司.

A contact can belong to 1 company.

更多调整:

我做了一些修改，但它返回了1条记录.应该返回2.无论电子邮件地址是否存在关系，我都需要它来拉出记录.

I have made some amendments but it is returning 1 record. should return 2. I need it to pull out the record whether or not a relationship exists for email address.

SELECT
    contacts.id AS CONTACTID,
    accounts.id AS ACCOUNTID,
    contacts.first_name,
    contacts.last_name,
    contacts.phone_work,
    contacts.phone_fax,
    contacts.department,
    contacts.title,
    contacts.description,
    contacts.salutation,
    EM.email_address,
    contacts.deleted,
    EABR.primary_address
FROM
    contacts
LEFT JOIN accounts_contacts ON contacts.id = accounts_contacts.contact_id
JOIN accounts ON accounts.id = accounts_contacts.account_id
LEFT JOIN email_addr_bean_rel EABR ON contacts.id = EABR.bean_id
AND (
    EABR.primary_address = 1
    || (EABR.primary_address IS NOT NULL AND EABR.primary_address != 0)
) 
JOIN email_addresses EM ON EABR.email_address_id = EM.id
WHERE
    contacts.first_name = 'shamraiz'

已解决的答案:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_contacts_sugar_hdb`
AS
select
    `hdb`.`contacts`.`CONTACTID` AS `CONTACTID`,
    `hdb`.`contacts`.`CLIENTID` AS `CLIENTID`,
     concat(`hdb`.`contacts`.`FIRSTNAME`,_utf8' ',coalesce(`hdb`.`contacts`.`INITIALS`,_utf8'')) AS `FIRSTNAME`,
    `hdb`.`contacts`.`LASTNAME` AS `LASTNAME`,
    `hdb`.`contacts`.`PHONE` AS `PHONE`,
    `hdb`.`contacts`.`FAX` AS `FAX`,
    `hdb`.`contacts`.`DEPARTMENT` AS `DEPARTMENT`,
    `hdb`.`contacts`.`TITLE` AS `TITLE`,
    `hdb`.`contacts`.`INFO` AS `INFO`,
    `hdb`.`contacts`.`SALUTATION` AS `SALUTATION`,
    `hdb`.`contacts`.`EMAIL` AS `EMAIL`,
    CASE
        WHEN `hdb`.`contacts`.`ACTIVE` != 0 THEN 0
        ELSE 1
    END DELETED,
    'paradox' AS `SOURCEDATABASE`
    from `hdb`.`contacts`
    union
        SELECT
        contacts.id AS CONTACTID,
        accounts_contacts.account_id AS CLIENTID,
        contacts.first_name AS FIRSTNAME,
        contacts.last_name AS LASTNAME,
        contacts.phone_work AS PHONE,
        contacts.phone_fax AS FAX,
        contacts.department AS DEPARTMENT,
        contacts.title AS TITLE,
        contacts.description AS INFO,
        contacts.salutation AS SALUTATION,
        email_addresses.email_address AS EMAIL,
        contacts.deleted AS DELETED,
        'sugar' AS SOURCEDATABASE
    FROM
        (
            (
                (
                    sugarcrm.contacts
                    LEFT JOIN sugarcrm.email_addr_bean_rel ON (
                        (
                            contacts.id = email_addr_bean_rel.bean_id
                        )
                    )
                    AND (
                        email_addr_bean_rel.primary_address = 1 || (
                            email_addr_bean_rel.primary_address IS NOT NULL
                            AND email_addr_bean_rel.primary_address != 0
                        )
                    )
                )
                LEFT JOIN sugarcrm.accounts_contacts ON (
                    (
                        contacts.id = accounts_contacts.contact_id
                    )
                )
            )
            JOIN sugarcrm.email_addresses ON (
                (
                    email_addr_bean_rel.email_address_id = email_addresses.id
                )
            )
        )
    LEFT JOIN sugarcrm.accounts ON accounts.id = accounts_contacts.account_id
ORDER BY
    `LASTNAME`,
    `FIRSTNAME`;

推荐答案

SELECT
       contacts.id AS CONTACTID,
       accounts.id AS ACCOUNTID,
       contacts.first_name,
       contacts.last_name,
       contacts.phone_work,
       contacts.phone_fax,
       contacts.department,
       contacts.title,
       contacts.description,
       contacts.salutation,
       email_addresses.email_address,
       contacts.deleted
   FROM
      contacts
         INNER JOIN accounts_contacts 
            ON contacts.id = accounts_contacts.contact_id
            JOIN accounts
               ON accounts.id = accounts_contacts.account_id
         INNER JOIN email_addr_bean_rel EABR
            ON contacts.id = EABR.bean_id
            INNER JOIN email_addresses EM
               ON EABR.email_address_id = EM.id
   WHERE 
      contacts.first_name = 'shamraiz' 

就像我为您提供帮助的其他问题一样……

Just like the other questions I've helped you on...

一次列出一个表，将INNER JOIN(或LEFT JOIN)与下一个表"ON"，无论与这两个表相关的条件如何...然后，将INNER JOIN(或LEFT JOIN)与关系中的下一个表层次结构.

List one table at a time, INNER JOIN (or LEFT JOIN) to the next table "ON" whatever criteria that those two tables relate... Then, INNER JOIN (or LEFT JOIN) to the next table in the relationship hierarchy.

如果您有同一个人的多个联系人记录，例如不同的帐户和/或电子邮件，您将获得多个记录.

If you have multiple contact records for the same person, such as different accounts and/or emails, you WILL get multiple records.

这篇关于sugarcrm简单查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！