Joining Tables on Multiple Values

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

General SQL Server Forums

New to SQL Server Programming

Joining Tables on Multiple Values

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-09-19 : 10:12:35

hi there - I have the following tables (I've simplified it):

Table 1 (T1):

TEL_NUMBER ID
5145551234 AB4CA
9055559978 B00FA
4165554567 FD471
7805551237 157D9

Table 2 (T2)

TEL_NUMBER REGION
5145551234 PQ
5145551234 MTL
5145551234 PQ
9055559978 GTA
9055559978 ACTON
9055559978 BRADFORD
4165554567 GTA
4165554567 TORONTO
7805551237 WEST
7805551237 ALBERTA
7805551237 CALGARY

I'm trying join the tables together by the Telephone number. However the second table has multiple instances of the Telephone number (the "joining" value). The Telephone_num values are duplicated because there might be 3-4 different regions for each tel number.

I want the query to pull display/join based on the FIRST region it finds, and that's it.

How can I eliminate the duplicates from the result set? THANKS !!!!!!!!!!

My existing query:

SELECT DISTINCT T1.TEL_NUMBER, T2.REGION FROM T1
LEFT JOIN T2 ON T1.TEL_NUMBER=T2.TEL_NUMBER

Desired Result Set:

5145551234 PQ
9055559978 GTA
4165554567 GTA
7805551237 WEST

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 10:15:50

Hello,

Have a look at the 'DISTINCT' commmand.

HTH.

EDIT. When requirements change, so do appropriate responses... :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 10:33:32

quote:
Originally posted by funk.phenomena

I want the query to pull display/join based on the FIRST region it finds, and that's it.

How can I eliminate the duplicates from the result set? THANKS !!!!!!!!!!

Hello,

You mention that are looking for the 'first region it finds'. What is your ordering mechanism? Do you have a field you wish to sort by to pull the first one in the series?

If so, can you please specify the ordering column(s)?

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-09-19 : 12:54:11

There's no specific ordering requirements. Just the first record based on the record location in the table.

For example, the First record, It doesn't matter if it pulls up "PQ" or "MTL". Just as long as it pulls only one.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 12:57:51

quote:
Originally posted by funk.phenomena

There's no specific ordering requirements. Just the first record based on the record location in the table.

For example, the First record, It doesn't matter if it pulls up "PQ" or "MTL". Just as long as it pulls only one.

I see, how about something like this then?;


SELECT t1.TEL_NUMBER, MIN(t2.REGION) AS REGION
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.TEL_NUMBER = t1.TEL_NUMBER
GROUP BY t1.TEL_NUMBER

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-09-19 : 14:04:44

Thanks for your suggestion. It doesn't work because the region is still duplicated in the second table.

7805551237 WEST
7805551237 ALBERTA
7805551237 WEST
7805551237 WEST

When I use the MIN (or MAX) function, it will still generate a result set with multiple Telephone numbers:

7805551237 WEST
7805551237 ALBERTA

Any other Ideas?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 14:15:46

quote:
Originally posted by funk.phenomena

Thanks for your suggestion. It doesn't work because the region is still duplicated in the second table.

7805551237 WEST
7805551237 ALBERTA
7805551237 WEST
7805551237 WEST

When I use the MIN (or MAX) function, it will still generate a result set with multiple Telephone numbers:

7805551237 WEST
7805551237 ALBERTA

Any other Ideas?

When testing such a query, I do not see such a result.


create table #t1 (TEL_NUMBER varchar(20), ID varchar(20))
insert #t1
select '5145551234','AB4CA'
union all select '9055559978','B00FA'
union all select '4165554567','FD471'
union all select '7805551237','157D9'

create table #t2 (TEL_NUMBER varchar(20), REGION varchar(20))

insert #t2
select '5145551234','PQ'
union all select '5145551234','MTL'
union all select '5145551234','PQ'
union all select '9055559978','GTA'
union all select '9055559978','ACTON'
union all select '9055559978','BRADFORD'
union all select '4165554567','GTA'
union all select '4165554567','TORONTO'
union all select '7805551237','WEST'
union all select '7805551237','WEST'
union all select '7805551237','WEST'
union all select '7805551237','WEST'
union all select '7805551237','ALBERTA'
union all select '7805551237','CALGARY'

SELECT t1.TEL_NUMBER, MIN(t2.REGION) AS REGION
FROM #t1 t1
LEFT JOIN #t2 t2 ON t2.TEL_NUMBER = t1.TEL_NUMBER
GROUP BY t1.TEL_NUMBER


drop table #t1, #t2

Perhaps you have other characters in your phone numbers which are making them distinct? I cannot tell.

Sorry I could not be of more help for you. I hope you find a solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources