CODEWITHSUNDEEP
sql
Atzin
Atzin

Reputation: 125

Select rows that contain data from any of the rows in another table

I am attempting to return all the email addresses that contain any of the values from another table.

SELECT EMAIL
FROM 
  DBO.EMAIL_ADDRESSES AS A,
  DBO.EMAIL_VALUE_LOOKUP AS B
WHERE 
  POSITION(B.EMAIL_VALUE IN A.EMAIL) <> 0

Upvotes: 0

Views: 35

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Let's roll forward about 30 years:

SELECT e.email
FROM 
  dob.email_addresses e
  INNER JOIN dbo.email_value_lookup l ON e.email LIKE '%' + l.email_value + '%'

You might want to use DISTINCT too, as if emails match multiple different values they will be repeated. This could be done implicitly by using a coordinated Exists query:

SELECT e.email
FROM 
  dob.email_addresses e
WHERE EXISTS(
  SELECT null FROM dbo.email_value_lookup l WHERE e.email LIKE '%' + l.email_value + '%')

Upvotes: 2

Related Questions