What should be a simple table comparison...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nintendomasta811@gmail.com - 17 Jul 2006 13:24 GMT

Hello everyone,

I have a simple dilemma that I just can't seem to solve. I have two
tables: tblfromXLS and tblwebmaster. I need to use an SQL statement
that will check the variable SSN (used in both tables) in tblfromXLS
against the variable SSN in tblwebmaster. Basically, I need to
determine if a row's SSN in tblfromXLS has any matches in tblwebmaster
and if NOT then insert the row into tblwebmaster. This cannot be that
complicated, but I am running into problems. Currently I am using the
statement:
"SELECT * From tblFromXLS WHERE NOT EXISTS (SELECT * FROM tblfromXLS
WHERE tblfromXLS.SSN=tblwebmaster.SSN);". However, whenever I run the
statement I get an inputbox asking me for the value of tblwebmaster.SSN
(SQL is not detecting the value it seems). Thanks in advance.

Reply to this Message

John Spencer - 17 Jul 2006 13:39 GMT

How about

SELECT tblFromXLS.*
FROM tblFromXLS LEFT JOIN tblWebMaster
ON tblfromXLS.SSN=tblwebmaster.SSN
WHERE tblwebMaster.SSN is Null

Your query does not reference the table tblWebMaster in the FROM clause of
the query. You could rewrite it to
SELECT * From tblFromXLS
WHERE SSN NOT IN
(SELECT SSN FROM tblWebMaster)

or to

SELECT * From tblFromXLS
WHERE Not Exists
(SELECT * FROM tblWebMaster
WHERE tblWebMaster.SSN = tblFromXLS.SSN)

> Hello everyone,
>
[quoted text clipped - 10 lines]
> statement I get an inputbox asking me for the value of tblwebmaster.SSN
> (SQL is not detecting the value it seems). Thanks in advance.

Reply to this Message

nintendomasta811@gmail.com - 17 Jul 2006 16:56 GMT

Thank you. The SQL you suggested worked perfectly.

> How about
>
[quoted text clipped - 29 lines]
> > statement I get an inputbox asking me for the value of tblwebmaster.SSN
> > (SQL is not detecting the value it seems). Thanks in advance.

Reply to this Message