SQL Query to pull only Hard Bounces?

Question

I currently manually update a database by exporting bounced email addresses using the code below:

SELECT DISTINCT UPPER(EmailAddress) as EmailAddress, SubscriberKey
FROM _Subscribers
WHERE Status = 'held'
OR Status = 'bounced'

We want to change this so we only get hard bounces and can still email addresses with a soft bounce. The BounceCategory column appears to be attributed to another table - how do I update the code?

Apologies if something similar has been asked, I'm brand new to this and very lost.

Answer 1

You will need to join up to the _Bounce data view.

SELECT DISTINCT UPPER(s.EmailAddress) as EmailAddress, 
s.SubscriberKey, 
b.BounceCategory
FROM _Subscribers as s
INNER JOIN _Bounce as b
ON b.SubscriberKey = s.SubscriberKey
WHERE b.BounceCategory = 'Hard bounce'

To add in a column from an additional data extension you can just add another join in the query.

SELECT DISTINCT UPPER(s.EmailAddress) as EmailAddress, 
s.SubscriberKey, 
b.BounceCategory,
bh.PROMO_KEY
FROM _Subscribers as s
INNER JOIN _Bounce as b
ON b.SubscriberKey = s.SubscriberKey
INNER JOIN BounceHeld as bh
ON bh.SubscriberKey = s.SubscriberKey
WHERE b.BounceCategory = 'Hard bounce'