Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2004-09-26
Medium Priority
541 Views
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.

dt

0
Question by:tippingpoint
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 15

Expert Comment

ID: 12154341

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

LVL 15

Expert Comment

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...

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,",")

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

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

fritz_the_blank earned 2000 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

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

Question has a verified solution.

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

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…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
###### Suggested Courses
Course of the Month6 days, 2 hours left to enroll