How to get username based on foreign key id in SQL Server

Hi gokuldas,

Please explain your requirement in more detail exactly what you are trying to achieve?

If possible explain your requirement with an example.

I have two tables Tbl_users ,Tbl_Credit.

In  Tbl_users  I have username,Id etc

Tbl_Credit i have refernceid as Crediitfrom .

 I Need to get username instead of reference id in following code

select  
u.username  as TransactionWith
,c.UserId
,Credit= CASE  WHEN c.Type=  '0' THEN '0' Else (Amount) End
,Debit= CASE WHEN c.Type= '0' THEN -(Amount) Else   '0' End 
,c.CreditFrom  as Reference
,Dt as Date  
from  Tbl_Credit c ,Tbl_users u  
where u.Id=c.UserId and u.Id=29

union

select distinct  Top 10 
u.username  as TransactionWith
,c.UserId
,Credit= CASE  WHEN c.Type=  '0' THEN (Amount) Else 0 End
,Debit= CASE WHEN c.Type= '0' THEN 0 Else   -(Amount) End 
,c.CreditFrom as Reference
,Dt as Date  
from  Tbl_Credit c,Tbl_users u 
where u.Id=c.UserId  and c.Type='1'   and u.Id=29

Hi gokuldas,

Refer below SQL Query.

SQL

SELECT  
u.username  as TransactionWith
,c.UserId
,Credit = CASE WHEN c.Type = '0' THEN '0' Else (Amount) End
,Debit = CASE WHEN c.Type = '0' THEN -(Amount) Else '0' End
,(SELECT us.username FROM Tbl_users us WHERE us.UserId = c.CreditFrom) as Reference
,Dt as Date  
FROM  Tbl_Credit c ,Tbl_users u  
WHERE u.Id = c.UserId AND u.Id = 29
 
UNION
 
SELECT DISTINCT  Top 10
u.username  as TransactionWith
,c.UserId
,Credit = CASE WHEN c.Type = '0' THEN (Amount) Else 0 End
,Debit = CASE WHEN c.Type = '0' THEN 0 Else -(Amount) End
,(SELECT us.username FROM Tbl_users us WHERE us.UserId = c.CreditFrom) as Reference
,Dt as Date  
FROM  Tbl_Credit c,Tbl_users u
WHERE u.Id = c.UserId AND c.Type='1' AND u.Id = 29