MySQL一个表内连接有3个不同的实体表

I got 4 tables, Email_Company_Contact_Ref table is the table which linked with Email, Company, and Contact.

我有4个表,Email_Company_Contact_Ref表是与电子邮件,公司和联系人链接的表。

**Email_Company_Contact_Ref**

id = primary key
email_id = reference to Email.`id`
ref_id = it can be Company.id / Contact.id
table = reference from which table name

I try to use left join to get my output, but I got duplicated result. If I try inner join, I will not get any result at all, it is because Company and Contact this two tables does not have any thing common.

我尝试使用左连接来获取输出,但我得到了重复的结果。如果我尝试内部联接,我将不会得到任何结果,这是因为公司和联系这两个表没有任何共同点。

This is the output I would like to complete.

这是我想要完成的输出。

I able to use UNION to get the output, but it not really effective. I am thinking it should be a way to get the output result.. Please help.

我能够使用UNION来获取输出,但它并不真正有效。我认为它应该是一种获得输出结果的方法..请帮助。

Thanks!

3 个解决方案

#1

Here is my mysql answer, hope this can help

这是我的mysql答案,希望这可以提供帮助

SELECT e.email, r.table, c1.name AS company_name, c2.name AS contact_name
FROM email_company_contact_ref r
JOIN email e ON e.id = r.email_id
LEFT JOIN company c1 ON (c1.id = r.ref_id AND r.table = 'company')
LEFT JOIN contact c2 ON (c2.id = r.ref_id AND r.table = 'contact')
GROUP BY r.table, e.email

#2

I don't think it can be done without a UNION. Here's my suggestion.

我不认为没有UNION就可以做到。这是我的建议。

SELECT email_address, eccr.table table, company_name, contact_name
FROM Email e, Email_Company_Contact_Ref eccr,
     (SELECT "Company" table, id, company_name, NULL contact_name
      FROM Company
      UNION ALL
      SELECT "Contact" table, id, NULL company_name, contact_name
      FROM Contact) cc
WHERE e.id = eccr.email_id
AND eccr.table = cc.table
AND eccr.email_id = cc.id

#3

I'm not getting the ref_id part... Is it a foreign key? Or is that the primary key for the Email_Company_Contact_Ref table?

我没有得到ref_id部分......它是外键吗?或者这是Email_Company_Contact_Ref表的主键?

I would think you'd want to put the reference for the Email table in the Company and Contact tables. If you need more than one emails for them, then you should create two join tables: Company_Email and Contact_Email. Your current design (with references to table names as values for a column) is bad SQL design -- just because things like RoR promote it, it won't get any better.

我认为你想在公司和联系人表格中提供电子邮件表格的参考。如果您需要多封电子邮件,则应创建两个连接表:Company_Email和Contact_Email。您当前的设计(将表名称作为列的值引用)是错误的SQL设计 - 只是因为像RoR这样的东西促进了它,它不会变得更好。

With proper design, the equivalent of that complicated query would look something like:

通过适当的设计,相当于复杂的查询将类似于:

CREATE TABLE Company_Email (company_id integer, email_address varchar(100),
  FOREIGN KEY company_id REFERENCES Company (id));

CREATE TABLE Contact_Email (contact_id integer, email_address varchar(100),
  FOREIGN KEY contact_id REFERENCES Contact (id));

SELECT email_address, 'Company' AS kind, company_name AS name
  FROM Company_Email ce JOIN Company c ON company_id = c.id
 UNION
SELECT email_address, 'Contact', contact_name
  FROM Contact_Email ce JOIN Contact c ON contact_id = c.id;

If you can't change it, you'll have to do the UNION along the lines Barmar explained it.

如果你不能改变它,你将不得不按照Barmar解释的那样做UNION。

Or, you can do a SELECT DISTINCT to get rid of the duplicates from your left joined query.

或者,您可以执行SELECT DISTINCT以删除左连接查询中的重复项。