如何获取MYSQL中至少一个相关表中的数据(How to obtain data that is in at least one table of several related in MYSQL)

如何获取MYSQL中至少一个相关表中的数据(How to obtain data that is in at least one table of several related in MYSQL)

我需要关于SQL的一个问题的帮助。

我有三个表格，表格1，表格2，表格3都带有一个“电子邮件”字段，它们相互关联。

另一方面，我有一个包含电子邮件列表的数组，我必须检查它们是否在其中一个表中。 这些电子邮件还将它们保存在用逗号分隔的字符串中，并用它们各自的引号将其用于IN（....）中：

$emails = ("'email1@example.com', 'email2@example.com', ...."); SELECT * FROM Table t1 INNER JOIN Table2 t2 ON t1.email = t2.email INNER JOIN Table3 t3 ON t1.email = t3.email WHERE (t1.email IN ($ emails)) OR (t2.email IN ($ emails) ) OR (t3.email IN ($ emails));

在前面的例子中，我意识到，当进行INNER JOIN时，我通过电子邮件字段匹配表中的所有表中没有值的表。 可能是电子邮件不在某个表中，而是在另一个表中，因此我需要SQL查询，以便获取3个表中任意一个的电子邮件。

我被告知，我做了一个电子邮件数组的临时表，然后在表格之间做了LEFT JOIN，但我不知道该怎么做。

你能给我一个如何解决这个问题的想法吗？

提前致谢。

I need help with a problem I have about SQL.

I have three tables, Table1, Table2, Table3 all with an "email" field with which they relate to each other.

On the other hand, I have an array with a list of emails that I have to check if they are in one of those tables. These emails also have them saved in a string separated with commas and with their respective quotes to use it in an IN (....):

$emails = ("'email1@example.com', 'email2@example.com', ...."); SELECT * FROM Table t1 INNER JOIN Table2 t2 ON t1.email = t2.email INNER JOIN Table3 t3 ON t1.email = t3.email WHERE (t1.email IN ($ emails)) OR (t2.email IN ($ emails) ) OR (t3.email IN ($ emails));

With the previous example, I realized that when doing an INNER JOIN I was matching the tables by the email field which did not have the value in all the tables. It may be the case that an email is not in some table but in another yes, so I need an SQL query so that I get the emails that are in ANY of the 3 tables.

I have been told that I made a temporary table of the emails array and then doing LEFT JOIN between the tables I could get it, but I do not know how to do it.

Could you give me an idea of ​​how to solve this problem?

Thanks in advance.

最满意答案

你需要使用UNION ，而不是JOIN 。

SELECT email, "Table1" AS whichTable FROM Table1 WHERE email in ($email) UNION SELECT email, "Table2" AS whichTable FROM Table2 WHERE email in ($email) UNION SELECT email, "Table3" AS whichTable FROM Table3 WHERE email in ($email)

在所有查询中使用SELECT *假定所有表具有相同顺序的相似列。 如果不是，则需要在每个查询中明确列出列。

You need to use UNION, not JOIN.

SELECT email, "Table1" AS whichTable FROM Table1 WHERE email in ($email) UNION SELECT email, "Table2" AS whichTable FROM Table2 WHERE email in ($email) UNION SELECT email, "Table3" AS whichTable FROM Table3 WHERE email in ($email)

Using SELECT * in all the queries assumes that all the tables have similar columns in the same order. If not, you'll need to list the columns explicitly in each query.