Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Query several fields in one table from another table

Posted on 2013-12-03
2
Medium Priority
?
301 Views
Last Modified: 2013-12-03
Hi,

I have a table looking like this:
customerID int
firstname
lastname
address
zip
city

Then I have a second table looking like this:
id
name (Smith, John)
adress
zip
city

what I need to do is to get all records from the first table that are not part of the second one based on firstname, lastname and city. The name is stored in a firstname and a lastname in the first but they are in one column in the second.

How can I accomplish this in a query?

Peter
0
Comment
Question by:peternordberg
2 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 39692181
select * from firsttable A
where not exists(select 1 from secondtable B
           where A.lastname + ', ' + A.firstname = B.name
           and A.city = B.city)
0
 

Author Closing Comment

by:peternordberg
ID: 39692272
Thanks,

just what I needed.

Peter
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

824 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