Changing collation within a query

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)

Changing collation within a query

Author

Topic

dark_perfect
Starting Member

4 Posts

Posted - 2011-02-25 : 05:56:11

Hi,

We have a query that resembles this :

SELECT "User_Id",
FROM "dbo"."Table_A"
WHERE "User_Id" COLLATE DATABASE_DEFAULT NOT IN (SELECT "User_Id"
FROM "dbo"."Table B"
WHERE Attr_B = 'VALIDATED'
)
ORDER BY 1

The collation of Table_A.User_Id is Latin1_General_CI_AS, but the collation of Table_B.User_Id is SQL_Latin1_General_CP1_CI_AS. We can't change the collation on these columns (don't ask why - my boss has said so is the simple reason!).

If I have a user_id of "dark_perfect" in Table-A but NOT in Table_B, I would expect this query to return that record as a row. It doesn't - the query always seems to return 0 rows, and we're having trouble getting it to return anything other than this!

I have rewritten the query to this instead :

SELECT A."User_Id"
FROM "dbo"."Table_A" A
LEFT JOIN "Table_B" B
ON A."User_Id" = B."_User_Id" COLLATE DATABASE_DEFAULT
AND Attr_B = 'VALIDATED'
WHERE B.User_Id IS NULL
ORDER BY 1

This query SEEMS to work, as far as I can tell (it returns resultsets that look to be correct, although I haven't done any in depth analysis to confirm this).

I'm interested in knowing why the first query doesn't return the result set that I would expect? Can anyone help me with this?

Thanks,
Dark_Perfect

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-25 : 06:21:36

[code]SELECT a.User_ID,
FROM dbo.Table_A AS a
WHERE NOT EXISTS (
SELECT *
FROM dbo.Table_B AS b
WHERE b.User_ID = a.User_ID COLLATE DATABASE_DEFAULT
AND b.Attr_B = 'VALIDATED'
)
ORDER BY a.User_ID[/code]

N 56°04'39.26"
E 12°55'05.63"

dark_perfect
Starting Member

4 Posts

Posted - 2011-02-25 : 06:41:21

quote:

Originally posted by Peso

SELECT		a.User_ID,
FROM		dbo.Table_A AS a
WHERE		NOT EXISTS	(
					SELECT	*
					FROM	dbo.Table_B AS b
						WHERE	b.User_ID = a.User_ID COLLATE DATABASE_DEFAULT
						AND b.Attr_B = 'VALIDATED'
				)
ORDER BY	a.User_ID

N 56°04'39.26"
E 12°55'05.63"

Peso,

Many thanks! This works perfectly, and is much more preferred to my own alternative. I'm afraid I do not understand why the first query doesn't work, however. Would someone be able to explain this or point me to another thread/article which might?

Many thanks,
Dark_perfect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources