在多个表中查找包含电子邮件的所有行(Finding all rows with e-mails over multiple tables)
我有一个包含多个包含电子邮件字段的表的数据库。 现在,让我们说它们被称为table1,table2和table3。
我想要一个查询,在所有表上的所有行上查找特定域(... @ domain.com)。
我用来查看哪些表有电子邮件行的查询:
Select * From INFORMATION_SCHEMA.COLUMNS WHERE column_name = 'email';
这将返回table1,table2,table3的列表。
我用来查看哪些行包含我要搜索的域的查询:
Select * from table1 where email like '%@domain.com;
这将返回table1中所有行的列表,其中电子邮件在域上结束。
我正在寻找的是一个查询,它将table1,table2和table3的结果组合在一个列表中。 我认为会起作用的是:
Select * from ( Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'email' ) where email like '%domain.com'
但是这会返回一个错误:
[42000][1248] Every derived table must have its own alias
所以我想我只是写出来的问题:
select * from table1 where email like '%@domain.com' OR select * from table2 where email like '%@domain.com' OR select * from table3 where email like '%@domain.com';
这会导致语法错误。
我搜索了subquerys但我无法让这个查询起作用。 谁能帮我吗?
在此先感谢您的时间。
I have a database with multiple tables containing an e-mail field. For now, lets say they are called table1, table2 and table3.
I want an query that finds specific domains (...@domain.com) over all rows, over all tables.
The query i use to see which tables have an e-mail row:
Select * From INFORMATION_SCHEMA.COLUMNS WHERE column_name = 'email';
This returns a list of table1, table2, table3.
the query i use to see which rows contain the domain i am searching for:
Select * from table1 where email like '%@domain.com;
This returns a list from all rows in table1 which e-mail ends on the domain.
What i'm looking for is a query that combines the results of table1, table2 and table3 in one list. What i thought would work was:
Select * from ( Select * From INFORMATION_SCHEMA.COLUMNS Where column_name = 'email' ) where email like '%domain.com'
But this returns an error:
[42000][1248] Every derived table must have its own alias
So i thought i'd just write the querys out:
select * from table1 where email like '%@domain.com' OR select * from table2 where email like '%@domain.com' OR select * from table3 where email like '%@domain.com';
This results in an syntax error.
I have searched for subquerys but i am unable to get this query to work. Can anyone help me out?
Thanks in advance for your time.
原文:https://stackoverflow.com/questions/51170175