2个SQL表之间没有通用ID的哪种联接? [英] What kind of join between 2 SQL table that they don't have common id?
问题描述
我有一个包含ID，名称，电子邮件...的SQL表....我还有另一个具有ID，电子邮件，emailstatus的SQL表，但是这2个ID不同，它们没有关系.这两个表之间唯一的共同点是电子邮件.
I have a SQL table consists of id, name, email,.... I have another SQL table that has id, email, emailstatus but these 2 id are different they are not related. The only thing that is common between these 2 tables are emails.
我想加入这2个表，以带走表1中的所有信息，如果表1和表2中的电子邮件地址相同，并且emailstatus为退回".但是我正在写的查询给了我比预期更多的记录.
I would like to join these 2 tables bring all the info from table1 and if the email address from table 1 and table 2 are same and emailstatus is 'Bounced'. But the query that I am writing gives me more record than expected.
可以帮忙吗?同样，我什至不确定没有通用ID的做法是否正确.这是我的查询.
Can you help? Also I am not even sure that it is the right thing to do without a common id. This is my query.
SELECT
A.[Id]
,A.[Application]
,A.[Loan]
,A.[Firstname]
,A.[Lastname]
,A.[Email],
,H.[Email], H.[EmailStatus] as BouncedEmail
FROM Applicant A (NOLOCK)
left outer join [tbl_Webhook] [H] (NOLOCK)
on A.Email = H.Email
and A.Email is not Null and H.Email is not Null and H.[EmailStatus]='bounced'
所需数据样本:
id email name emailFromTable2 emailstatus
1 test2@yahoo.com lili test2@yahoo.com bounced
2 tesere@yahoo.com mike Null Null
3 tedfd2@yahoo.com nik tedfd2@yahoo.com bounced
4 tdfdft2@yahoo.com sam Null Null
5 tedft2@yahoo.com james tedft2@yahoo.com bounced
6 tedft2@yahoo.com San Null
推荐答案
您的根本问题是:
找出申请人中的每条记录是否都收到过退回的电子邮件."
"Find out whether every record in Applicant has ever had a bounced email."
以下查询将为您提供所需的输出(未反弹时为NULL值，否则为反弹").
The following query should get you your desired output (NULL values when not bounced, 'bounced' otherwise).
SELECT A.id, A.name, A.email, B.emailstatus
FROM Applicant A
LEFT OUTER JOIN
(
SELECT DISTINCT email, emailstatus
FROM tbl_webhook
) B
ON A.email = B.email
AND B.emailstatus = 'bounced'
一些注意事项:
- 加入非id字段很好.您可能需要考虑的唯一一件事就是性能(因为id字段往往是主键或主键的一部分).除非您注意到它会引起问题，否则我不会对此太担心.如果事实证明这是一个问题，则在两个表的电子邮件上引入索引将有所帮助.
- 使用B.emailstatus ='bounced'的LEFT OUTER JOIN可以确保联接中仅包含emailstatus被退回的行(这意味着，即使john@test.com处于电子邮件状态，他仍然在NULL中显示为NULL结果集，因为他的状态不是被退回".
- SQL Fiddle(下面的链接)是一种通过互联网与人们交流SQL问题的绝佳方法.
- 使用DISTINCT删除相同的行.由于tbl_webhook中有多个退回的电子邮件记录，这将阻止您获得多个相同的记录.
SQL提琴架构:
CREATE TABLE Applicant (id int, name varchar(10), email varchar(100))
INSERT INTO Applicant VALUES(1, 'John', 'john@test.com')
INSERT INTO Applicant VALUES(2, 'Bob', 'bob@test.com')
INSERT INTO Applicant VALUES(3, 'Mary', 'mary@test.com')
INSERT INTO Applicant VALUES(4, 'Jane', 'jane@test.com')
CREATE TABLE tbl_Webhook (id varchar(2), email varchar(100), emailstatus varchar(10))
INSERT INTO tbl_Webhook VALUES('AA', 'john@test.com', 'received')
INSERT INTO tbl_Webhook VALUES('BB', 'bob@test.com', 'bounced')
INSERT INTO tbl_Webhook VALUES('CC', 'mary@test.com', 'sent')
这篇关于2个SQL表之间没有通用ID的哪种联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
