Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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
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
Asked:
-
3
-
2
1 Solution
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
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
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....
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
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
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....
ooops some typo
change this line
For x=LBound(arraystrcompare) to LBound(arraystrcompare)
with this
For x=LBound(arraystrcompare) to UBound(arraystrcompare)
change this line
For x=LBound(arraystrcompare) to LBound(arraystrcompare)
with this
For x=LBound(arraystrcompare) to UBound(arraystrcompare)
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
SELECT email FROM TBL1 WHERE email NOT IN (SELECT email FROM TBL2)
FtB
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
http://www.w3schools.com/sql/sql_select_into.asp
SELECT email INTO TBL3 FROM TBL1 WHERE email NOT IN (SELECT email FROM TBL2)
FtB
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.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
http://www.experts-exchange.com/Databases/MS_Access/Q_20950529.html