March 21, 2022 at 10:46 pm
Hello!
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation
I have two tables -
Table1 as linuxdata ( only one column and that is UID - nvarchar(50)
Table2 as coumputerdata (columns: id (uniqueidentifier) , name(nvarchar(63), importid(nvarchar(80) .. and so on)
UID of table1 and importdid of table2 has same data(different columns names)
I want to see UID of table1 with its associated name from importid of table2 in my output.
Here is my query and I get collation error.
SELECT fc.importid, fc.name
FROM dbo.computerdata fc
LEFT JOIN dbo.linuxdata sd ON fc.importid=sd.uid
please help!
March 22, 2022 at 8:31 am
You need to cast the join columns to the same collation. Ideally you should sort out the collation in the database as casting negates the use of indexes.
As you have not posted the table structure the following casts both columns.
SELECT fc.importid, fc.[name]
FROM dbo.computerdata fc
LEFT JOIN dbo.linuxdata sd
ON fc.importid COLLATE SQL_Latin1_General_CP1_CI_AS
= sd.[uid] COLLATE SQL_Latin1_General_CP1_CI_AS;
March 22, 2022 at 2:26 pm
Thank you Ken.. collation error is gone. But my output is different than what I'm expecting thou!
UID column in table(linuxdata) and importid column in table(computerdata) has same information , just column name is different.
I want UID column in linuxdata table to map with the importid column in computerdata table and give the output of UID( from linuxdata table ) with its name(column from table2)
please let me if I'm explaining correct!
March 22, 2022 at 4:16 pm
I want UID column in linuxdata table to map with the importid column in computerdata table and give the output of UID( from linuxdata table ) with its name(column from table2)
The query already gives you the UID and the name, however, as there is a left join I would rearrange the tables. In theory this may return some null values for importid and [name]. If there is no possibility of nulls, an inner join could be used and it would not matter which order the tables are listed. (I assume the computerdata table already has the server default collation so doesn't need to be cast).
SELECT sd.uid,
fc.importid,
fc.[name]
FROM dbo.linuxdata AS sd
LEFT OUTER JOIN dbo.computerdata AS fc ON sd.[uid] COLLATE SQL_Latin1_General_CP1_CI_AS = fc.importid;
March 22, 2022 at 5:24 pm
yes, this is the output I was looking for. since importid and UID has same information , I removed fc.importid in the beginning as below.
SELECT sd.[UID],fc.name
FROM dbo.linuxdata sd
LEFT JOIN dbo.computerdata fc
on fc.importid COLLATE SQL_Latin1_General_CP1_CI_AS
= sd.uid COLLATE SQL_Latin1_General_CP1_CI_AS
Thank you very much for your help and support! I just started learning sql and this is already driving me crazy but its fun thou!
March 23, 2022 at 6:47 pm
So now i have different requirement that I'm trying to solve , same scenario but little modifications on the requirments.
instead of having table 1 in sql server, Lets assume i have that table outside server in a location and i want to ready that as csv or something using python. How can we compare with the csv file from outside and foundcomputer_table in sql server to get the same output as we discussed above ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply