July 17, 2010 at 8:43 am
Hi,
I have a transaction table where I have 'receivedby' and 'orderedby' columns. Both the columns are referring the StaffID of Emptable.
I have to get received by and ordered by in the same query with the relevant names.
declare @staff table(id int identity(1,1), Name varchar(10))
insert into @staff values('abc')
insert into @staff values('def')
insert into @staff values('ghi')
insert into @staff values('klm')
declare @order table(id1 int identity(1,1), orderby int, receivedby int)
insert into @order values( 1,2)
insert into @order values( 2,2)
insert into @order values( 3,2)
insert into @order values( 4,2)
insert into @order values( 4,4)
select * from @staff
select * from @order
select id1
,orderby
,m.Name orderbyname
,m1.Name receivedbyname
from @staff m
inner join @order t
on m.id = t.orderby
inner join @staff m1
on t.receivedby = m.id
In the above code i would like to get the name of the staff in the place of order by and received by.
Thanks,
Ami
July 17, 2010 at 9:12 am
What would be your expected result other than what you already have?
Isn't the result already what you're looking for?
July 18, 2010 at 12:13 am
Hi,
I would like to get result like
1abcdef
2defdef
3ghidef
4klmdef
5klmklm
where the relevant names for the orderedby and receivedby staffids.
thanks,
ami
July 18, 2010 at 12:57 am
All you have to do is replace the m in the last line with m1 in your query
select id1
,orderby
,m.Name orderbyname
,m1.Name receivedbyname
from @staff m
inner join @order t
on m.id = t.orderby
inner join @staff m1
on t.receivedby = m1.id
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply