Solved

Compairing two tables t-sql

Posted on 2008-06-09
6
191 Views
Last Modified: 2010-03-19
Hi,
I have a table that holds data on address information,it also has a column which shows what type of product is on the address.
This table has been created based on a coverage db  ,and now i would like to compare this table to my own database.
My own db has address information and information on clients and products.

I would like to show all the information from the coverage table and show how many of these address compare to the address in my db .
I tried using INTERSECT and just plain joins but because there could be one address line in my db that has the same address lines it seems to return duplicate rows...
Please can some one give me some advice on this.

thanks
0
Comment
Question by:Putoch
  • 4
6 Comments
 
LVL 22

Expert Comment

by:dportas
Comment Utility
INTERSECT never returns duplicate rows. It does an implicit DISTINCT.

SELECT col1, col2, col3
FROM Address1
INTERSECT
SELECT col1, col2, col3
FROM Address2;

If you need more help please post more information: CREATE TABLE statement(s) (simplified but including keys) plus some sample data.
0
 

Author Comment

by:Putoch
Comment Utility
ok thanks , i will post the create table examples now.
0
 

Author Comment

by:Putoch
Comment Utility
Here are the Create tables and just some examples;

CREATE TABLE [dbo].[COVERAGE_CHECK](
      [id] [bigint] NULL,
      [orderID] [bigint] NULL,
      [address_1] [nvarchar](255) NULL,
      [address_2] [nvarchar](255) NULL,
      [address_3] [nvarchar](255) NULL,
      [address_4] [nvarchar](255) NULL,
      [town] [nvarchar](200) NULL,
      [county] [nvarchar](100) NULL
) ON [PRIMARY]


INSERT INTO [COVERAGE_CHECK] values (1,123,Fenniscourt,null,null,Bagenalstown,Carlow)
INSERT INTO [COVERAGE_CHECK] values (2,124,High Street,null,null,Bagenalstown,Carlow)


Create Account (
account_id int,
product_id int,
customer_id int)
insert into account values (621681,7,1111)
insert into account values (345654,7,1101)

Create Customer(
customer_id int,
address_1 varchar(30),
address_2 varchar(30),
address_3 varchar(30),
town varchar(50),
county varchar(30))
insert into customer values(1111,Fenniscourt,null,null,Bagenalstown,Carlow)
insert into customer values(1101,The railwayhouse,null,null,Bagenalstown,Carlow)

create product(
product_id,
product)
insert into Product values(7,Ripwave)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Putoch
Comment Utility
by the way the only way to try to compair the two  main table (ie Customer and Coverage Check) is using the address fields


SELECT bc.address_1,bc.address_2,bc.address_3,bc.town,bc.county
 FROM coverage_check bcc,
 AND (bcc.maps_matched_address is NULL
OR bcc.maps_matched_address = 'No Address Match Found' )

 
INTERSECT

Select c.address_1,c.address_2, c.address_3,c.town,c.county
from boss_customer c
inner join boss_account a on a.customer_id = c.customer_id
inner join boss_product p on p.product_id = a.product_id
where p.produc_id = 7

0
 
LVL 18

Accepted Solution

by:
lludden earned 200 total points
Comment Utility
Something like this returns all the orders in COVERAGE_CHECK with their address and shows which address (if any) they have in the customer table.

SELECT     COVERAGE_CHECK.orderID, COVERAGE_CHECK.address_1, COVERAGE_CHECK.town, COVERAGE_CHECK.county, product.product, 

                      Customer.address_1 AS Expr1, Customer.town AS Expr2, Customer.county AS Expr3

FROM         Customer INNER JOIN

                      Account ON Customer.customer_id = Account.customer_id INNER JOIN

                      product ON Account.product_id = product.product_id RIGHT OUTER JOIN

                      COVERAGE_CHECK ON Customer.address_1 = COVERAGE_CHECK.address_1 AND Customer.town = COVERAGE_CHECK.town AND 

                      Customer.county = COVERAGE_CHECK.county

Open in new window

0
 

Author Comment

by:Putoch
Comment Utility
thank you for your help.
that worked.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now