Solved

Compairing two tables t-sql

Posted on 2008-06-09
6
211 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
ID: 21747016
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
ID: 21747025
ok thanks , i will post the create table examples now.
0
 

Author Comment

by:Putoch
ID: 21747396
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Putoch
ID: 21747541
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
ID: 21747567
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
ID: 21747811
thank you for your help.
that worked.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 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