Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Compairing two tables t-sql

Posted on 2008-06-09
6
Medium Priority
?
251 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 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