DECLARE	@Table1 TABLE
(
	T1_ID		int,
	T1_MatchColumn1	varchar(30),
	T1_MatchColumn2	varchar(30),
	PRIMARY KEY
	(
		T1_ID
	)
)

DECLARE	@Table2 TABLE
(
	T2_ID		int,
	T2_ShouldMatch	int,
	T2_ValueColumn1	varchar(30),
	PRIMARY KEY
	(
		T2_ID
	)
)

INSERT INTO @Table1
	  SELECT	1, 'Value 1', 'OtherValue 1'
UNION ALL SELECT	2, 'Value 2', 'OtherValue 2'
UNION ALL SELECT	3, 'Value X1', 'Value X2'
UNION ALL SELECT	4, 'Value X2', 'Value X1'

INSERT INTO @Table2
	  SELECT	1,1, 'Value 1'
UNION ALL SELECT	2,1, 'OtherValue 1'
UNION ALL SELECT	3,2, 'Value 2'
UNION ALL SELECT	4,2, 'OtherValue 2'
UNION ALL SELECT	5,3, 'Value X1'
UNION ALL SELECT	6,4, 'Value X2'

PRINT '[1] Flawed - matches 5 & 6 twice'
SELECT	CASE WHEN T2_ShouldMatch = T1_ID THEN '' ELSE 'ERR' END,
	*
FROM	@Table2 AS T2
	JOIN @Table1 AS T1
		ON T1_MatchColumn1 = T2_ValueColumn1
		OR T1_MatchColumn2 = T2_ValueColumn1
--
ORDER BY T2_ID, T1_ID

PRINT '[2] Flawed - fails to match 2 & 4'
SELECT	CASE WHEN T2_ShouldMatch = T1_ID THEN '' ELSE 'ERR' END,
	*
FROM	@Table2 AS T2
	JOIN @Table1 AS T1
		ON COALESCE(T1_MatchColumn1, T1_MatchColumn2) = T2_ValueColumn1
--
ORDER BY T2_ID, T1_ID


PRINT '[3] Works OK but too clunky?'
SELECT	CASE WHEN T2_ShouldMatch = T1_ID THEN '' ELSE 'ERR' END,
	*
FROM	@Table2 AS T2
	JOIN
	(
		SELECT	T1_ID,
			T1_MatchColumn
		FROM
		(
			SELECT	[T_RowNumber] = ROW_NUMBER()
					OVER
					(
						PARTITION BY T1_MatchColumn 
						ORDER BY T1_MatchColumn, T1_Source
					),
				X.*
			FROM
			(
				SELECT	T1_ID, [T1_MatchColumn] = T1_MatchColumn1, [T1_Source] = 1
				FROM	@Table1
				UNION ALL
				SELECT	T1_ID, T1_MatchColumn2, 2
				FROM	@Table1
			) AS X
		) AS T
		WHERE	T_RowNumber = 1

	) AS T1
		ON T1_MatchColumn = T2_ValueColumn1
--
ORDER BY T2_ID, T1_ID

quote:

---

Originally posted by Kristen

Thanks Visakh, I prefer that as I can easily index both columns, and the pair of JOINs will thus be efficient.

I had had a similar thought already and hit a snag (the real world data has some wider implications than my test sample), but I'll give it a crack and come back if I hit a brick wall.

Has Graz fixed the 99,999 post bug for you yet?

---