Compare and Select query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Compare and Select query

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2011-08-04 : 08:27:44

Hi All

I have to come up with a select query for the following scenario.
There are 2 tables "TempMismatch" and "TempData".

"TempMismatch" contains code and its mismatching code. 1 code can have multiple mismatching codes.

CREATE TABLE #TempMismatch ( Original varchar(10),Mismatch varchar(10))

INSERT INTO #TempMismatch (Original,Mismatch)
SELECT 'AA','BB'
INSERT INTO #TempMismatch (Original,Mismatch)
SELECT 'XX','YY'
INSERT INTO #TempMismatch (Original,Mismatch)
SELECT 'CC','DD'
INSERT INTO #TempMismatch (Original,Mismatch)
SELECT 'EE','FF'
INSERT INTO #TempMismatch (Original,Mismatch)
SELECT 'AA','XX'
"TempData" contains just the codes in a single column.

CREATE TABLE #TempData ( Code varchar(10))

INSERT INTO #TempData (Code)
SELECT 'AA'
INSERT INTO #TempData (Code)
SELECT 'BB'
INSERT INTO #TempData (Code)
SELECT 'CC'
INSERT INTO #TempData (Code)
SELECT 'XX'
INSERT INTO #TempData (Code)
SELECT 'YY'

Select * from #TempMismatch
Select * from #TempData

--Drop table #TempMismatch
--Drop table #TempData

Now for every “code” in #TempData I need to select the corresponding row from #TempMismatch where “code” equals “Original”
And the “Original”’s “mismatch” is present as one of the rows in #TempData.

So in the above example. The result would be.

AA,BB
AA,XX
XX,YY

Higly appriciate your help with respect to building the best possible query for the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 08:47:42

isnt this a matter of simple join between tables on condition
#TempData.Code=#TempMismatch.Original

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

kneekill
Yak Posting Veteran

76 Posts

Posted - 2011-08-04 : 08:57:00

quote:
Originally posted by visakh16

isnt this a matter of simple join between tables on condition
#TempData.Code=#TempMismatch.Original

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

Hi visakh

the normal condition would also return

CC,DD

As DD is not in the "TempData" table I do not want it to be returned.

Inner join would return

AA BB
AA XX
CC DD
XX YY

right join would return

NULL NULL
AA BB
AA XX
CC DD
XX YY

and left join would return

AA BB
AA XX
CC DD
EE FF
XX YY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:02:42

nope. this should work


SELECT tm.Original,tm.Mismatch
FROM #TempData td1
JOIN #TempMismatch tm
ON tm.Original = td1.Code
JOIN #TempData td2
ON td2.Code = tm.Mismatch
ORDER BY tm.Original,tm.Mismatch

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

kneekill
Yak Posting Veteran

76 Posts

Posted - 2011-08-04 : 09:25:44

Thanks a lot Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-04 : 09:33:18

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources