CODEWITHSUNDEEP
sqlsql-server
sebu
sebu

Reputation: 2954

SQL Left Join not working with char data type based on conditional value

I have two tables Nationality and Student. Both tables joined by NationalityID char(3). char holds the spaces like: '1 '. here total 3 chars for both tables. but no result even though they have same common value. i have also trim the blank space but no result. please see the query.

SELECT S.StudentID,N.NationalityName
FROM [dbo].[Student] S
LEFT JOIN [dbo].[Nationality] N  
ON S.NationalityID=N.NationalityID
WHERE S.StudentID=232

--No Output

After using RTRIM()

SELECT S.StudentID,N.NationalityName
FROM [dbo].[Student] S
LEFT JOIN [dbo].[Nationality] N  
ON RTRIM(S.NationalityID)=RTRIM(N.NationalityID)
WHERE S.StudentID=232

--No Output

Upvotes: 0

Views: 217

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Clearly, they don't have the same value and you should fix the data. One possible problem is spaces at the beginning and/or end. If so, try:

SELECT S.StudentID, N.NationalityName
FROM [dbo].[Student] S LEFT JOIN
     [dbo].[Nationality] N  
     ON LTRIM(RTRIM(S.NationalityID)) = LTRIM(RTRIM(N.NationalityID))
WHERE S.StudentID = 232;

I'm not so confident that this will work. Assuming that the values are ASCII characters, you can use the ASCII function to see what is really being stored:

SELECT S.NationalityID, ASCII(LEFT(S.NationalityID, 1)),
       ASCII(SUBSTRING(S.NationalityID, 2, 1)),
       ASCII(RIGHT(S.NationalityID, 1))
FROM Student s;

Once you have figured this out, then fix the data so the joins will work. By the way, you would not be having this problem if NationalityID were an identity column.

Upvotes: 1

Related Questions