Solved

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

Posted on 2004-09-26
5
534 Views
Last Modified: 2009-12-16
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

0
Comment
Question by:tippingpoint
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:gladxml
ID: 12154341
try to check out the link might help... regarding about join...

http://www.experts-exchange.com/Databases/MS_Access/Q_20950529.html
0
 
LVL 15

Expert Comment

by:gladxml
ID: 12154425
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
 
LVL 15

Expert Comment

by:gladxml
ID: 12154428
ooops some typo

change this line

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

with this

For x=LBound(arraystrcompare) to UBound(arraystrcompare)
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 12154632
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
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12154647
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

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question