Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Compairing two tables t-sql

Posted on 2008-06-09
6
Medium Priority
?
249 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

636 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