[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Extract emails based on domain on another table.

DB: Access 2002.
I only have two simple tables.

Table A
- email  [string]

Table B
- domain [string]

----------------------------------
Table A Sample Data
- abc@hotmail.com
- abc@NotThisDomain.com
- abc@yahoo.com
- abc@NotThisToo.com
- abc@yahoo.com.au
----------------------------------
Table B Sample Data
- hotmail.com
- yahoo.com
----------------------------------

I want a method (Query or function call to create new table) to extract the listed Domains in Table B out from Table A.

Wanted Result Table
- abc@hotmail.com
- abc@yahoo.com
- abc@yahoo.com.au


0
dennisdominic
Asked:
dennisdominic
  • 6
  • 4
1 Solution
 
peter57rCommented:
Hi dennisdominic,

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


Pete
0
 
RimvisCommented:
Try this:

MS Access version:
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('*@' + B.domain + '*');

MS SQL (and other DBs):
SELECT A.email
FROM A INNER JOIN B ON A.email LIKE ('%@' + B.domain + '%');

0
 
dennisdominicAuthor Commented:


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
%>
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
RimvisCommented:
Ther's an erros in your script
'%@%" & rs1("Domain") & "%'" - extra '%' after @
Should be
'%@" & rs1("Domain") & "%'" - extra '%' after @
0
 
RimvisCommented:
Oops
Should be
'%@" & rs1("Domain") & "%'"
0
 
dennisdominicAuthor Commented:
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.

0
 
RimvisCommented:
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
0
 
dennisdominicAuthor Commented:
'%@" & 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..



0
 
RimvisCommented:
>>  I was hoping to capture those abc@uk.yahoo.com abc@yahoo.com.uk and all variations
Last % DOES it.
0
 
dennisdominicAuthor Commented:
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.
0
 
RimvisCommented:
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 + '%');
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now