Joining Tables with Multiple - SQL Server Forums

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 with Multiple

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-08-17 : 17:25:02

I am in the process of joining two tables:

TABLE #1

TEL_NUM
4165551234
4165551235
4165551236
4165551237

TABLE #2

TEL_NUM REGION
4165551234 NORTHWEST
4165551234 NORTHEAST
4165551235 SOUTHWEST
4165551235 SOUTHWEST
4165551235 SOUTHEAST
4165551236 NORTHEAST
4165551236 NORTHWEST
4165551237 SOUTHWEST
4165551237 NORTH
4165551237 SOUTH

I am joining the two tables based on Telephone numbers. However the second table has multiple instances of the telephone number. I only need it to pull the distinct telephone number, and join ONLY on the first available math, so the output is as follows:

4165551234 NORTHWEST
4165551235 SOUTHWEST
4165551236 NORTHEAST
4165551237 SOUTHWEST

SELECT * FROM T1
LEFT JOIN T2 ON T1.TEL_NUM = T2.TEL_NUM

How can I achieve this? Do I change the join type?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-17 : 17:35:02

The values are not distinct in second table. Check if this helps you

SELECT t2.*
FROM T1
Inner Join (SELECT Distinct Tel_Number,Region FROM Table2) T2 on T1.Tel_Number=t2.Tel_Number

Still, you may find multiple values because same tel numbers are associated with different regions in T2. However this may help you in getting onto right direction.

Cheers
MIK

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 18:31:05

Assuming that Table #2 has an Identity, or some other column that describes the "order" in your list, which I've called [T2_ID], then this will do:


SELECT	T.TEL_NUM,
	T.REGION
FROM
(
	SELECT	[T_RowNumber] = ROW_NUMBER()
			OVER
			(
				PARTITION BY X.TEL_NUM 
				ORDER BY X.TEL_NUM, X.T2_ID
			),
		X.*
	FROM
	(
		SELECT	T1.TEL_NUM,
			T2.T2_ID,
			T2.REGION
		FROM	TABLE_1 AS T1
			JOIN TABLE_2 AS T2
				ON T1.TEL_NUM = T2.TEL_NUM
		WHERE	1=1
	) AS X
) AS T
WHERE	T_RowNumber <= 1
ORDER BY T.TEL_NUM

otherwise just leave out "T2.T2_ID," and you'll get a random single row from Table #2 for each number in Table #1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:15:25

as Kirsten pointed out, unless you've a unique valued column you cant guarantee the order of returned region value. there's no concept of first or last in a sql table unless you specify the order by mean of a ORDER BY clause.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources