检查两个表中的字符串 [英] check string in two tables

问题描述

嗨专家，



我有2个表（客户，用户），每个表都有[电子邮件]字段，没有可加入的常用字段。

我希望能够检查任何一个中是否存在给定的电子邮件，例如：

Hi Experts,

I have 2 tables (customers, users) each of them has [Email] Field, no common fields to join.
I want to be able to check if a given email exists in either one, something like:

select Id from customers where customers.email='email' or user.email='email'
OR
return true if @email exists in (customers or users)

寻找相关ct语法。



提前致谢！

Samira

looking for the correct syntax.

Thanks in advance!
Samira

推荐答案

替代方案是联合电子邮件地址，例如

An alternative would be to union the email addresses e.g.

DECLARE @Result int

--Dummy data
declare @tbl1 table (id1 int identity(1,1), email1 varchar(30))
declare @tbl2 table (id2 int identity(1,1), email2 varchar(30))
insert into @tbl1  values('email1'),('email2'),('email3')
insert into @tbl2  values('email4'),('email2'),('email5')

if exists (select top 1 * from (select email1 from @tbl1 union select email2 from @tbl2) t where email1=@email)
    set @Result= 1
else
    set @Result= 0

 return @Result

另一种方法是以这种方式使用数据透视表：

Another way is to use pivot table this way:

SELECT TableName, [Customers], [User] 
FROM (
    SELECT 'Customers' AS TableName, ID, Email
    FROM Customers
    UNION ALL
    SELECT 'User' AS TableName, ID, Email
    FROM User
) AS DT
PIVOT (MAX(ID) FOR TableName IN ([Customers], [User])) AS PT
WHERE Email = @email

最好使用几个简单的不存在

best use a couple of simple "not exists"

declare @email nvarchar(max)
set @email = '[e-mail]'

IF  not exists (select * from customers where customers.email=@email) 
and not exists (select * from users where users.email=@email) 
  return 1
else
  return 0

希望有所帮助^ _ ^

Andy

hope that helps ^_^
Andy

这篇关于检查两个表中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！