• Last Updated:
  • Jan 6th, 2010 5:16 pm
[OP]
Deal Addict
Jul 30, 2003
1572 posts
100 upvotes

SQL Help

Its been quite a while since i last used SQL, to say I'm rusty is an understatement.

I have two tables "Denver09Patron" and "DenverRenewed"

Common field names between the two tables are the follow:

Last Name
First Name
Telephone

I need an sql statement that extracts all the names & phone number from "Denver09Patron" that is not in the "DenverRenewed"

Thanks for all the help.
...life is nothing more than a journey through the consequences of our actions...

Kingsway Image Instagram || Gallery
10 replies
Member
Dec 23, 2006
314 posts
27 upvotes
Hmmm...

You can try this:

Select * from Denver09Patron D09
where NOT Exists(Select * from DenverRenewed DR where DR.FirstName = D09.FirstName and DR.LastName = D09.LastName and DR.Telephone = D09.Telephone)

That should give you all rows from Denver09Patron where no record exists in DenverRenewed with the same Name & Phone Number.
Deal Guru
User avatar
May 9, 2006
12450 posts
3169 upvotes
My SQL is a little rusty (do most of my db work in SAS), but aren't you missing "AS"? OR can it be implied?
javis wrote: Select * from Denver09Patron AS D09
where NOT Exists(Select * from DenverRenewed AS DR where DR.FirstName = D09.FirstName and DR.LastName = D09.LastName and DR.Telephone = D09.Telephone)
Member
Dec 23, 2006
314 posts
27 upvotes
joeyjoejoe wrote: My SQL is a little rusty (do most of my db work in SAS), but aren't you missing "AS"? OR can it be implied?
It is implied in SQL Server. You have to use it if you are renaming columns but not for giving tables abbreviations.
[OP]
Deal Addict
Jul 30, 2003
1572 posts
100 upvotes
Sorry forgot to mention I'm just using Microsoft Access. I've already imported the data into two different tables named accordingly.
...life is nothing more than a journey through the consequences of our actions...

Kingsway Image Instagram || Gallery
Deal Guru
User avatar
May 9, 2006
12450 posts
3169 upvotes
NLI10D wrote: Sorry forgot to mention I'm just using Microsoft Access. I've already imported the data into two different tables named accordingly.
Then you are probably running into problems because your column names have spaces. You will need some square brackets around those... like so:

Select * from Denver09Patron AS D09
where NOT Exists(Select * from DenverRenewed AS DR where DR.[First Name] = D09.[First Name] and DR.[Last Name] = D09.[Last Name] and DR.Telephone = D09.Telephone);

You will also need a semicolon at the end.
[OP]
Deal Addict
Jul 30, 2003
1572 posts
100 upvotes
tried the last post. record went from 605 to 604. Did not work
...life is nothing more than a journey through the consequences of our actions...

Kingsway Image Instagram || Gallery
Member
Dec 23, 2006
314 posts
27 upvotes
NLI10D wrote: tried the last post. record went from 605 to 604. Did not work
What do you mean? Did you get 604 matches?
Deal Guru
User avatar
May 9, 2006
12450 posts
3169 upvotes
NLI10D wrote: tried the last post. record went from 605 to 604. Did not work
From the info you provided, that's the correct code.

I suspect that you have problems in your data. You are matching on first name, last name and phone number. That's pretty hard to match on exactly. What if there are spelling mistakes in the name or the name is entered as "David Smith" in one table and "Dave Smith" in the other.... "D. Smith" or "David J. Smith"? None of those will match.

Also the phone number... is it standardized? Or does it look like this:

(416) 222-2222
416-222-2222
4162222222
416 222 222
416-222-2222 x222

All the phone number examples from above should all match, but won't with a direct match. Also what if they changed their phone number from the first file to the renewed file? Are you sure that there are no other fields? Like a policy number?
[OP]
Deal Addict
Jul 30, 2003
1572 posts
100 upvotes
thank you everyone for all your help. as joeyjoejoe from above post pointed out, it was a phone number issue and i took that check out.

Thank you guys, you have no idea how much work you guys just saved me. Now does anyone have an SQL that would call 241 people in a matter of minutes :D
...life is nothing more than a journey through the consequences of our actions...

Kingsway Image Instagram || Gallery

Top

Thread Information

There is currently 1 user viewing this thread. (0 members and 1 guest)