?
Solved

Compairing two tables t-sql

Posted on 2008-06-09
6
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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