CODEWITHSUNDEEP
sqlsql-serversql-server-2008
nikta
nikta

Reputation: 79

how use distinct in second join table in sql server

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.

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 I expected because there are multiple rows in tbl_webhook(second table) for each row in Applicant(first table) .I want to know if applicant has EVER had an email bounce.

Query without join shows 23000 record but after join shows 42000 record that is because of duplicate how I can keep same 23000 record only add info from second table?

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 H.[event]='bounced'

this is sample of desired data:

id   email               name    emailFromTable2    emailstatus
1    [email protected]     lili    [email protected]    bounced
2    [email protected]    mike    Null               Null
3    [email protected]    nik     [email protected]   bounced
4    [email protected]   sam     Null               Null              
5    [email protected]    james   [email protected]   bounced
6    [email protected]    San     Null

Upvotes: 1

Views: 2875

Answers (3)

Low Chee Mun
Low Chee Mun

Reputation: 610

i believe query below should be enough to select distinct bounced email for you, cheer :)

SELECT
A.[Id]
,A.[Application]
,A.[Loan]
,A.[Firstname]
,A.[Lastname]
,A.[Email],
,H.[Email], H.[EmailStatus] as BouncedEmail 
FROM Applicant A (NOLOCK)

Inner join [tbl_Webhook] [H] (NOLOCK)
on A.Email = H.Email
and H.[EmailStatus]='bounced'

basically i just change the joining to inner join and change the 2nd table condition from event to emailstatus, if u can provide your table structure and sample data i believe i can help you up :)

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

;WITH DistinctEmails
AS
  (
  SELECT * , rn =  ROW_NUMBER() OVER (PARTITION BY [Email] ORDER BY [Email])
  FROM [tbl_Webhook]
  )
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 DistinctEmails [H] (NOLOCK)
on A.Email = H.Email  
WHERE H.rn = 1
and H.[event]='bounced'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a nested select for this type of query. I would write this as:

select id, application, load, firstname, lastname, email,
       (case when BouncedEmail is not null then email end) as EmailFromTable2,
       BouncedEmail
from (SELECT A.[Id], A.[Application], A.[Loan], A.[Firstname], A.[Lastname], A.[Email],
             (case when exists (select 1
                                from tbl_WebHook h
                                where A.Email = H.Email and H.[event] = 'bounced'
                               )
                   then 'bounced
              end) as BouncedEmail
      FROM Applicant A (NOLOCK)
     ) a

You can also do this with cross apply, but because you only really need one column, a correlated subquery also works.

Upvotes: 1

Related Questions