saf21 saf21 - 1 month ago 6
SQL Question

SQL server: select and join table where relationship is varchar and unique identifier

I got this below table from database. I'm quite new guys in this field, so I don't understand how to join table when they used 'unique identifier' in the table as relationship where user_login_id (varchar(20)) and id (uniqueidentifier).

enter image description here

Table: user_lookup



username | user_type | user_login_id
-------------------------------------
jdoe | Type_A | ultimate


Table: receiver



id | username | name
-----------------------------------------------------------
10D3C530-B1FA-41B8-A9F6-6C9B84B10DC5 | jdoe | john doe


How do I join this table ON user_lookup.user_login_id = receiver.id. I try below code but didn't get any result. Can anyone help me? thanks

SELECT *
FROM user_lookup tabA
JOIN receiver tabB
ON tabA.user_login_id = CAST(tabB.id AS VARCHAR(50))
WHERE tabA.username = 'ultimate'

Answer

Only common column I found between two table is username

SELECT *
FROM user_lookup tabA
    JOIN receiver tabB
    ON tabA.username = tabB.username 
WHERE tabA.user_login_id = 'ultimate'