SQL - compare 2 tables, if email in tbl1 exist in tbl2 then don't show, move next

Hi,

I have 2 tables. 1 is a membership(TBL1) table and the other is a list(TBL2) of unsubscribing people.

I need an SQL command to match the emails in TBL2 to TBL1 and output the remainder.

EG:

BEFORE
--------------
TBL1                                      TBL2
email1                                    email2
email2                                    email4
email3
email4
email5

AFTER
--------------
TBL3                            
email1
email3
email5


Logically,

If field(email) in TBL1 does not exist in TBL2, then show, then move next.


Thanks in advance,

dt

tippingpointAsked:
Who is Participating?
 
fritz_the_blankCommented:
I am wondering if you can do a nested select like this:

SELECT email FROM TBL1 WHERE email NOT IN (SELECT email FROM TBL2)


FtB
0
 
gladxmlCommented:
try to check out the link might help... regarding about join...

http://www.experts-exchange.com/Databases/MS_Access/Q_20950529.html
0
 
gladxmlCommented:
tippingpoint,

assuming that your connection is oConn... I think this logic will help if outer join is not applicable...  You can try it in your exisiting db just change the connection as well as the recordset object that you are currently using...


set rs1 = Server.CreateObject("ADODB.Recordset")
sql1 = "Select distinct email from TBL1 order by email"
rs1.Open sql1 , oConn, 1, 3

strcompare = ""
Do while not(rs1.eof )
strcompare = strcompare & "," & rs("email")
rs1.movenext
loop

arraystrcompare = split(strcompare,",")

set rs2 = Server.CreateObject("ADODB.Recordset")
sql2 = "SELECT distinct email FROM TBL2 order by email"
rs2.Open sql2 , oConn, 1, 3

do while not(rs2.eof)
countersame = 0
For x=LBound(arraystrcompare) to LBound(arraystrcompare)
if arraystrcompare(x) = rs2("email") then
countersame = countersame + 1
exit for
end if
next

if countersame = 0 then
response.write rs2("email") & "<br>"
end if

rs2.movenext
loop


Hope this help...

HAppy programming....
0
 
gladxmlCommented:
ooops some typo

change this line

For x=LBound(arraystrcompare) to LBound(arraystrcompare)

with this

For x=LBound(arraystrcompare) to UBound(arraystrcompare)
0
 
fritz_the_blankCommented:
If that worked, then we could just use the SELECT INTO clause to get what we need:

http://www.w3schools.com/sql/sql_select_into.asp



SELECT email INTO TBL3 FROM TBL1 WHERE email NOT IN (SELECT email FROM TBL2)


FtB
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here