Extract emails based on domain on another table.

Hi dennisdominic,

Select * from tableA
Where mid([email], instr([email],"@")+1) in (Select Domain from tableB)


Pete

On my own table.... Method from peter57r returns 81 rows and method from Rimvis returns 259 rows.

My own ASP Script below returns 399 rows.

<%
set rs1 = conn.execute("Select Domain from TableB")

while not rs1.eof
  sqlstr = "select email from TableA where email like '%@%" & rs1("Domain") & "%'"
  set rs2 = conn.execute(sqlstr)

    while not rs2.eof
        sqlstr="Insert INTO ResultTable (newEmail) values ('" & rs2("email") & "')"
        conn.execute(sqlstr)
        rs2.movenext                              
    wend                        

  rs1.movenext
wend
%>

Ther's an erros in your script
'%@%" & rs1("Domain") & "%'" - extra '%' after @
Should be
'%@" & rs1("Domain") & "%'" - extra '%' after @

Oops
Should be
'%@" & rs1("Domain") & "%'"

I can email the entire database (10mb) to you if you drop me an email at dennisdominc @ gmail . com  

Hoping for a quick and dirty SQL solution that I can implement on Query
or any ACCESS solution.

Check result with your corrected script.
This should work:
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@' + B.domain + '%');

BTW, question related communication via e-mail is against Member Agreement

'%@" & rs1("Domain") & "%'"
- 259... Cool... you are right. :-)


'%@%" & rs1("Domain") & "%'"
- 399 rows... I was hoping to capture those abc@uk.yahoo.com abc@yahoo.com.uk and all variations..

>>  I was hoping to capture those abc@uk.yahoo.com abc@yahoo.com.uk and all variations
Last % DOES it.

you need to fit a % (or *)  between @ and yahoo.com for uk. to go in...

don't email me :-p I didn't realise the rules.

Sorry, didn't got it, I was thinking about yahoo.com.uk.
In this case, you ARE right. Should be

SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@%' + B.domain + '%');