The problem I am currently experiencing is that if the Contact.email are different then it displays both as NULL. If I use OR instead of AND it acquires both of the emails but in different rows, so they are duplicated.
我目前遇到的问题是，如果Contact.email不同，那么它显示为NULL。如果我使用OR代替AND，它会获取两个电子邮件，但是在不同的行中，因此它们是重复的。
I think that I need to assign the alias but I'm not sure how to do that.
我认为我需要分配别名，但我不知道该怎么做。
SELECT Item.brought, Contact.email AS SalesContact, Contact.email AS TechContact
FROM Customer
LEFT JOIN Contact
ON Item.sales_id = Contact_id
AND Item.tech_id = Contact_id;
What I am trying to do is associate the Item.brought with the two possible email address which as stored in a different table. Any advice would be greatly predicated.
我想要做的是将Item.brought与存储在不同表中的两个可能的电子邮件地址相关联。任何建议都会有很大的预测。
Many thanks.
非常感谢。
1 个解决方案
#1
0
You are reading the same email. You need to join twice to Contact, like this:
您正在阅读同一封电子邮件。您需要加入两次联系人，如下所示：
SELECT Item.brought, SC.email AS SalesContact, TC.email AS TechContact
FROM Customer
LEFT JOIN Contact SC
ON Item.sales_id = SC.Contact_id
LEFT JOIN Contact TC
ON Item.tech_id = TC.Contact_id;
Explanation: You need two Contact records, which are not necessarily the same, since you can imagine a tech who is not also on sales and a sales who is not also a tech. So you are joining the sales guy using Item.sales_id and the tech guy using Item.tech_id.
说明：您需要两个联系人记录，这些记录不一定相同，因为您可以想象一个技术人员不是销售人员而且销售人员也不是技术人员。因此，您正在使用Item.sales_id和使用Item.tech_id的技术人员加入销售人员。
#1
0
You are reading the same email. You need to join twice to Contact, like this:
您正在阅读同一封电子邮件。您需要加入两次联系人，如下所示：
SELECT Item.brought, SC.email AS SalesContact, TC.email AS TechContact
FROM Customer
LEFT JOIN Contact SC
ON Item.sales_id = SC.Contact_id
LEFT JOIN Contact TC
ON Item.tech_id = TC.Contact_id;
Explanation: You need two Contact records, which are not necessarily the same, since you can imagine a tech who is not also on sales and a sales who is not also a tech. So you are joining the sales guy using Item.sales_id and the tech guy using Item.tech_id.
说明：您需要两个联系人记录，这些记录不一定相同，因为您可以想象一个技术人员不是销售人员而且销售人员也不是技术人员。因此，您正在使用Item.sales_id和使用Item.tech_id的技术人员加入销售人员。