0

I have 2 tables I am trying to join on PRId.

SELECT LOT_NUMBER 
FROM   Domain.dbo.LOT_TABLE
JOIN   Domain.dbo.SELINAS 
ON     Domain.dbo.SELINAS.PRId = Domain.dbo.LOT_TABLE.PRId;

When I run this it gives me following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

Adding COLLATE returns nothing:

SELECT LOT_NUMBER 
FROM   Domain.dbo.LOT_TABLE
JOIN   Domain.dbo.SELINAS 
  ON   Domain.dbo.SELINAS.PRId = Domain.dbo.LOT_TABLE.PRId 
COLLATE DATABASE_DEFAULT;

I tried adding Latin1_General_CI_AI and SQL_Latin1_General_CP1_CI_AS but it still returns nothing.

If I open the table columns and look at the Extended Properties of each column, they all appear to be SQL_Latin1_General_CP1_CI_AS.

It makes no sense...

I added 

d2rq:join "Domain.dbo.SELINAS.PRId COLLATE DATABASE_DEFAULT => Domain.dbo.LOT_TABLE.PRId COLLATE DATABASE_DEFAULT" ;

like one of you suggested and it couldn't find Domain.dbo.LOT_TABLE.PRId

Angelina
  • 2,175
  • 10
  • 42
  • 82

1 Answers1

2

Add collate clause for both fields, because it doesn't affect comparison operation itself, rather field values.

SELECT LOT_NUMBER 
FROM   Domain.dbo.LOT_TABLE
JOIN   Domain.dbo.SELINAS 
  ON   Domain.dbo.SELINAS.PRId COLLATE DATABASE_DEFAULT = Domain.dbo.LOT_TABLE.PRId COLLATE DATABASE_DEFAULT
AdamL
  • 12,421
  • 5
  • 50
  • 74
  • 1
    There is no need to add it on both sides. An explicit `COLLATE` clause on either side is sufficient. See [Collation Precedence](http://technet.microsoft.com/en-us/library/ms179886.aspx) – Martin Smith Sep 19 '13 at 13:42
  • 1
    @MartinSmith Actualy, it may be necessary if both the tables have different schema that db default, and different than each other; but one collate may suffice in this example, only moved to left side. – AdamL Sep 19 '13 at 13:45
  • 1
    An explicit collate clause on one side will take precedence over an implicit collation and the comparison will be carried out using the semantics of the explicit collation. – Martin Smith Sep 19 '13 at 13:48