0

I need to write a query to pull records which Email IDs are not matching in another table.

For example, I have a two tables whose structures looks something like this:

Table1
    ID    | Name    | Email ID
    101   | TOM     | TOM@gmail
    102   | KIM     | KIM@gmail
    103   | JIM     | JIM@gmail
    104   | RIM     | RIM@gmail
    105   | SIM     | SIM@gmail 

Table2
     ID   | Name    | Email ID
    101   | TOM     | TOMMOT@gmail
    102   | KIM     | KIM@gmail
    103   | JIM     | JIMMIJ@gmail
    104   | RIM     | RIM@gmail
    105   | SIM     | SIM@gmail `

So, I need to Pull only

    ID    | Name    | Email ID
    101   | TOM     | TOMMOT@gmail
    103   | JIM     | JIMMIJ@gmail
  • Hey, next time, if you could post your query -- working or not -- it'll make everyone feel better about pitching in and answering your question. – Adam Spriggs Feb 2 at 4:16
6

One way to do this is to leverage LEFT JOINS and subqueries

SELECT Id, Name, EmailId
FROM   (SELECT TABLE2.Id AS 'Id', TABLE2.Name AS 'Name', TABLE2.EmailId AS 'EmailId', TABLE1.Id AS 'SecondId'
        FROM   TABLE2
               LEFT JOIN TABLE1
                 ON TABLE2.EmailId = TABLE1.EmailID) AS myTable
WHERE  SecondId = Null

I have never actually written SQL against Automation Studio but this SQL statement is valid in T-SQL (MS SQL Server)

| improve this answer | |
6

You can also do it without explicit JOINs, if you don't need to return any values from the other Data Extension.

select
  t2.ID
, t2.Name
, t2.[Email ID]
from Table2 t2
where not exists (
  select top 1 t1.*
  from Table1 t1
  where t1.[Email ID] = t2.[Email ID]
)
| improve this answer | |
0

Thanks for your input, this query worked for me as I expected

select t2.ID , t2.Name , t2.[Email ID] from Table2 t2 where not exists ( select top 1 t1.* from Table1 t1 where t1.[Email ID] = t2.[Email ID] )

| improve this answer | |
  • 1
    Please mark the answer as accepted if it worked for you. – Adam Spriggs Feb 4 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.