How to join using unique rows in both tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to join using unique rows in both tables

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-02 : 09:16:30

I have two tables MyTable_A and MyTable_B. Both of these tables have Address and AddressID columns. There are many identical addresses in each respective table. The AddressID groups identical addresses so I can identify unique address.

I need a join that returns the rows that match between the two tables based on the MyTable_A.Address = MyTable_B.Address but only using unique AddressID’s from the two tables.

I have tried this query but the syntax is not right.

select max(a.phone), max(b.phone), max(a.address), max(b.address), max(b.LocationID)
from MyTable_A a
where exists(select distinct addressID from MyTable_B b)
group by a.AddressID

What is the correct syntax or should I be doing this a different way?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-02 : 10:42:10

In a relational database the whole point of having address and addressID values is so that the actual address only exists uniquely in one tabel and all other tables doesn't need to repeat the address value, they just need the addressID value.

So can we assume that all rows (from both tables) that share the same addressID value has identical address values? What about phone and locationID values? Will they always be the same for a given addressID? I would think that there could be many phones per address so is a random MAX(phone) sufficient for phone? Is there any unique identifier for a row from each table? Or a comination of columns that would make a row unique?

Why don't you post the table structures.
- Please use executalbe sql CREATE table statements
- Add a couple of samle rows using INSERT statements. Try to make the sample representative of the problem you are trying to solve.
- Then based on your sample data, type out the expected results from the statement you are looking for.

Be One with the Optimizer
TG

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-02 : 23:57:03

Yes the object of a relational DB is to have data exist in one place junction by keys. I inherited this DB and it will take some time to get it normalized.

The problem I have is matching redundant data between two tables. Both tables MyTable_A and MyTable_B are completely un-normalized. They contain a lot of redundant data and some unique data. I believe the files were originally large spread sheets that were imported into SQL and passed on to me.

In order to bring order to this ciaos I created a primary key in each table. Then created an AddressID to group and there for uniquely identify duplicate addresses with in each table. I did this with Phone and name also. These are not keys relating the two files. These ID’s only let me identify the unique addresses, names and phone numbers within the respective tables.

The first thing I wanted to do is determine the addresses that match between the two files. In order to do this I need to consider only unique AddressID’s in the respective files and match the actual address between the two files.

CREATE TABLE #MyTable_A (MyTable_AID INT PRIMARY KEY,
AddressID INT, Address Varchar(50),
Zip Varchar(10),
PhoneID INT, Phone Varchar(10),
NameID INT, Namex Varchar(50))

INSERT INTO #MyTable_A VALUES (1, 1, '123 Main', '12345', 1, '1231111111', 1, 'John Doe')
INSERT INTO #MyTable_A VALUES (2, 2, '222 North', '22222', 1, '2222222222', 2, 'Sue Kent')
INSERT INTO #MyTable_A VALUES (3, 1, '123 Main', '12345', 1, '1232222222', 3, 'Mary Doe')
INSERT INTO #MyTable_A VALUES (4, 3, '333 South', '33333', 1, '3333333333', 4, 'Frank Black')
INSERT INTO #MyTable_A VALUES (5, 1, '123 Main', '12345', 1, '1234444444', 5, 'John Smith')

CREATE TABLE #MyTable_B (MyTable_BID INT PRIMARY KEY,
AddressID INT, Address Varchar(50),
Zip Varchar(10),
PhoneID INT, Phone Varchar(10),
NameID INT, Namex Varchar(50))

INSERT INTO #MyTable_B VALUES (1, 5, '123 Main', '12345', 1, '1231111111', 1, 'Larry Franks')
INSERT INTO #MyTable_B VALUES (2, 2, '444 East', '44444', 2, '4444444444', 2, 'Frank Black')
INSERT INTO #MyTable_B VALUES (3, 5, '123 Main', '12345', 3, '1232222222', 3, 'Joe Little')
INSERT INTO #MyTable_B VALUES (4, 7, '333 South', '33333', 4, '3333333333', 2, 'Frank Black')
INSERT INTO #MyTable_B VALUES (5, 7, '333 South', '33333', 4, '3333333333', 4, 'Phil Johnson')

I want a result set that looks like this.
MyAddresses
MyAddresses_ID.....MTA_AID.....MTB_AID.....MTA_Address.....MTB_Address.....MTA_Zip.....MTB_Zip
1.......................1........5............123 Main........123 Main.......12345.......12345
2.......................3........7............333 South......333 South.......33333.......33333

Showing the data from both tables is just to check for errors. The extra columns would not be in the final result set.

Now that I know the addresses that match I can bring in the nonmatching addresses from both tables. This will give me an Address table containing unique addresses. I will do the same thing using the Phone and NameID to create Name and phone tables that junction to the address table. This will give me a normalized DB to work with.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 00:18:58

[code]CREATE TABLE #MyAddresses
(
MyAddresses_ID int IDENTITY(1,1),
MTA_AID int,
MTB_AID int,
MTA_Address Varchar(50),
MTB_Address Varchar(50),
MTA_Zip Varchar(10),
MTB_Zip Varchar(10)
)

INSERT INTO #MyAddresses (MTA_AID,MTB_AID,
MTA_Address,MTB_Address,
MTA_Zip,MTB_Zip)
SELECT d1.AddressID,d2.AddressID,
d1.Address.d2.Address,
d1.Zip,d2.Zip
FROM
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_A
)d1
INNER JOIN
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_B
)d2
ON d2.Address=d1.Address
AND d2.Zip=d1.Zip
AND d2.AddressID <> d1.AddressID

select * from #MyAddresses [/code]

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-03 : 01:04:43

When I run the suggested code I get this error:

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 01:20:12

quote:
Originally posted by kirknew2SQL

When I run the suggested code I get this error:

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

sorry there was a typo...try this:-

CREATE TABLE #MyAddresses
(
MyAddresses_ID int IDENTITY(1,1),
MTA_AID int,
MTB_AID int,
MTA_Address Varchar(50),
MTB_Address Varchar(50),
MTA_Zip Varchar(10),
MTB_Zip Varchar(10)
)

INSERT INTO #MyAddresses (MTA_AID,MTB_AID,
MTA_Address,MTB_Address,
MTA_Zip,MTB_Zip)
SELECT d1.AddressID,d2.AddressID,
       d1.Address,d2.Address,
       d1.Zip,d2.Zip
FROM
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_A 
)d1
INNER JOIN 
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_B 
)d2
ON d2.Address=d1.Address
AND d2.Zip=d1.Zip
AND d2.AddressID <> d1.AddressID

select * from #MyAddresses

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-03 : 02:02:13

I ran the suggested code. I did not get an error but #MyAddresses was empty. I modified the code removing "AND d2.AddressID <> d1.AddressID" Since the addressID's are not relate keys. then I got the fllowing result set:

1 1 1 123 Main 123 Main 12345 12345
2 3 3 333 South 333 South 33333 33333

The addresses are as expected, But the MTA_AID and MTB_AID are not. Based on the data shouldn't they be 1, 5 and 3, 7? There is no AddressID 1 for 123 Main or 3 for '333 South' in #MyTable_B

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:24:34

quote:
Originally posted by kirknew2SQL

I ran the suggested code. I did not get an error but #MyAddresses was empty. I modified the code removing "AND d2.AddressID <> d1.AddressID" Since the addressID's are not relate keys. then I got the fllowing result set:

1 1 1 123 Main 123 Main 12345 12345
2 3 3 333 South 333 South 33333 33333

The addresses are as expected, But the MTA_AID and MTB_AID are not. Based on the data shouldn't they be 1, 5 and 3, 7? There is no AddressID 1 for 123 Main or 3 for '333 South' in #MyTable_B

i gave the extra condition only to get back records with same Address and Zip and having different IDs (redundant records). If you are getting blank results then that means you dont have records with same address/zip and diffrent IDs existing in both tables. Another possibility would be that you might have extra leading or trailing spaces in your address/zip fields in one tables which prevents them from matching each other. I suggest you run this query to see if there are some unwanted spaces existing:-

SELECT d1.AddressID,d2.AddressID,
       d1.Address,d2.Address,
       d1.Zip,d2.Zip
FROM
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_A 
)d1
INNER JOIN 
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_B 
)d2
ON LTRIM(RTRIM(d2.Address))=LTRIM(RTRIM(d1.Address))
AND LTRIM(RTRIM(d2.Zip))=LTRIM(RTRIM(d1.Zip))
AND d2.AddressID <> d1.AddressID

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-03 : 07:34:12

Tehe resilts are correct. Two rows are suposed to be returned. The problem is the value in MTB_AID. There is no AddressID 1 for 123 Main or AddressID 3 for '333 South' in #MyTable_B. The rest of the data in the result set is correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:46:08

Thats because i'm guessing you dont have records with IDs 5 & 7 existing in second table that has exact address and zip values as 1 and 3 in table1.I'm hoping you used my exact query. Make sure you used column aliases correct.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-03 : 13:52:00

I am usin the exact query except for the one change removing "AND d2.AddressID <> d1.AddressID" Since the addressID's are not relate keys.

CREATE TABLE #MyAddresses
(
MyAddresses_ID int IDENTITY(1,1),
MTA_AID int,
MTB_AID int,
MTA_Address Varchar(50),
MTB_Address Varchar(50),
MTA_Zip Varchar(10),
MTB_Zip Varchar(10)
)

INSERT INTO #MyAddresses (MTA_AID,MTB_AID,
MTA_Address,MTB_Address,
MTA_Zip,MTB_Zip)
SELECT d1.AddressID,d2.AddressID,
d1.Address,d2.Address,
d1.Zip,d2.Zip
FROM
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_A
)d1
INNER JOIN
(
SELECT DISTINCT AddressID,Address,Zip
FROM #MyTable_B
)d2
ON d2.Address=d1.Address
AND d2.Zip=d1.Zip
AND d2.AddressID <> d1.AddressID

select * from #MyAddresses

Note in the test data that AddressIDs 5 & 7 exist in #MyTable_B and have the exact address and zip values as AddressIDs 1 and 3 in #MyTable_A

CREATE TABLE #MyTable_A (MyTable_AID INT PRIMARY KEY,
AddressID INT, Address Varchar(50),
Zip Varchar(10),
PhoneID INT, Phone Varchar(10),
NameID INT, Namex Varchar(50))

INSERT INTO #MyTable_A VALUES (1, 1, '123 Main', '12345', 1, '1231111111', 1, 'John Doe')
INSERT INTO #MyTable_A VALUES (2, 2, '222 North', '22222', 1, '2222222222', 2, 'Sue Kent')
INSERT INTO #MyTable_A VALUES (3, 1, '123 Main', '12345', 1, '1232222222', 3, 'Mary Doe')
INSERT INTO #MyTable_A VALUES (4, 3, '333 South', '33333', 1, '3333333333', 4, 'Frank Black')
INSERT INTO #MyTable_A VALUES (5, 1, '123 Main', '12345', 1, '1234444444', 5, 'John Smith')

CREATE TABLE #MyTable_B (MyTable_BID INT PRIMARY KEY,
AddressID INT, Address Varchar(50),
Zip Varchar(10),
PhoneID INT, Phone Varchar(10),
NameID INT, Namex Varchar(50))

INSERT INTO #MyTable_B VALUES (1, 5, '123 Main', '12345', 1, '1231111111', 1, 'Larry Franks')
INSERT INTO #MyTable_B VALUES (2, 2, '444 East', '44444', 2, '4444444444', 2, 'Frank Black')
INSERT INTO #MyTable_B VALUES (3, 5, '123 Main', '12345', 3, '1232222222', 3, 'Joe Little')
INSERT INTO #MyTable_B VALUES (4, 7, '333 South', '33333', 4, '3333333333', 2, 'Frank Black')
INSERT INTO #MyTable_B VALUES (5, 7, '333 South', '33333', 4, '3333333333', 4, 'Phil Johnson')

That is why the anticipater result set is...

MyAddresses_ID.....MTA_AID.....MTB_AID.....MTA_Address.....MTB_Address.....MTA_Zip.....MTB_Zip
1.......................1........5............123 Main........123 Main.......12345.......12345
2.......................3........7............333 South......333 South.......33333.......33333

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-03 : 22:17:23

I found the error I made. Thank you for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 23:07:55

You are welcome.Feel free to post a question whenever you face any difficulty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources