asked on

TSQL - How to use JOIN for two tables

Hi Guys,
I have 2 tables. How to join tables and get output: sender and recipient names based on documentId

--

CREATE TABLE Users
(
	 userId INT
	,name NVARCHAR
)
GO

INSERT INTO Users VALUES 
 (10001,'Darius')
,(10002,'John')
,(10003,'Peter')
,(20001,'Anna')
,(20002,'Stefan')
,(20003,'Adam')
GO

CREATE TABLE Documents  
(
	  documentId INT 
	, userSenderId INT
	, userRecipientId INT
)
GO

INSERT INTO Documents VALUES
 (500001,10001,20001)
,(500002,10002,20002)
,(500003,10003,20003)

--

Open in new window

output results NULL in the column for sender and recipient

declare @Tbl TABLE
(
	[DOC ID] int
	,[SENDER] nvarchar
	,[RECIPIENT] nvarchar);

INSERT into @Tbl 
	Select
		d.documentId,
		[SENDER] = CASE WHEN d.userSenderId is not null THEN u.name END,     --INCORECT HERE
		[RECIPIENT]= CASE WHEN d.userRecipientId is not null THEN u.name END    -- INCORECT HERE
	FROM Users u
		left join Documents d on u.userId in (d.senderUserId, d.recipientUserId)
		where d.documentId = 500002
		order by d.documentId desc
		
select * from @Tbl

Open in new window

1. Sender and Recipient NULL How to fix?
2. Any different solution on this JOIN

Thank you for advice!

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Darius

ASKER

Thank Paul!

Darius

ASKER

select
	  d.documentId
	, d.userSenderId
	, d.userRecipientId
        , s.name as SenderName
        , r.name as RecipientrName
from documents AS d
inner join users AS s on d.userSenderId = s.userId 
inner join users AS r on d.userRecipientId = s.userId

Open in new window

PortletPaul

sorry, the alias ds does not exist, the query should be like this:

select
	  d.documentId
	, d.userSenderId
	, d.userRecipientId
    , s.name as SenderName
    , r.name as RecipientrName
from documents AS d
inner join users AS s on d.userSenderId = s.userId 
inner join users AS r on d.userRecipientId = r.userId

Open in new window

The "trick" here is that you need to join to the Users table twice. To do that you MUST use an alias on the table to differentiate between the sender related data and the recipient related data.

PortletPaul

Darius, be careful that you do NOT join via s.userid twice. take not of my latest answer immediately above this comment.

Darius

ASKER

much appreciate!

Darius

ASKER

Something else here...

I tried to join more tables and not sure how to cast nvarchar with int

User has one more column 'accountNumber' (nvarchar)
recipientUserId as int

no error, but is no results...

--
. . .
		FROM Documents d 
			left join fieldvalues f on f.documentid = d.documentId 
			inner join Users us on d.senderUserId = us.userId
			inner join Users ur on d.recipientUserId = ur.userId
			inner join Users ura on d.recipientUserId = cast(cast(ura.accountNumber as float)as numeric(8,0))
			where d.documentid = 500002
--

Open in new window

works up to
inner join Users ura on d.recipientUserId = cast(cast(ura.accountNumber as float)as numeric(20,0))