SQL Query!

[tlin5043]

TBFundInOut
IDNO AccountNo
A-123456789, 'S-123456'
A-222222222, 'S-123456'
B-123456873, 'S-121212'
C-125343534, 'S-873645'



TBClientData
IDNO Name
A-123456789, 'Peter'
A-222222222, 'Chan'
B-123456873, 'Wong'
C-125343534, 'LEE'

what i want my query result:
AccountNo Name
'S-123456', Peter/Chan
'S-121212', Wong
'S-873645', LEE

thanks in advance
ricky

[skhanal]

Here's the query in ANSI syntax, it works for Access as well as SQL Server.

select TBF.AccountNo, TBC.Name
from TBFundInOut as TBF
inner join TBClientData as TBC
on TBF.IDNO = TBC.IDNO

[tlin5043]

hi skhanal,
i thought u misunderatand my question. i want my name looks like this
name1/name2/......
So the way u provided is not exactly,thanks in advance
ricky

[skhanal]

I don't think that is possible with a straight select statement, you will have to do row by row processing with a cursor/temp table and a while loop.

[MAK]

You can make use of function (SQL Server 2000).

create table TBFundInOut (IDNO varchar(20), AccountNo varchar(20))
insert into TBFundInOut select 'A-123456789', 'S-123456'
insert into TBFundInOut select 'A-222222222', 'S-123456'
insert into TBFundInOut select 'B-123456873', 'S-121212'
insert into TBFundInOut select 'C-125343534', 'S-873645'

Go
Create table TBClientData (IDNO varchar(20), Name varchar(20))
insert into TBClientData select 'A-123456789', 'Peter'
insert into TBClientData select 'A-222222222', 'Chan'
insert into TBClientData select 'B-123456873', 'Wong'
insert into TBClientData select 'C-125343534', 'LEE'

Go


create function dbo.slash (@AccountNo varchar(20) ) returns varchar(100)
as
begin
declare @x varchar(100)
set @x =""
select @x = @x + '/' +Name from TBClientData where IDNO in
(select IDNO from TBFundInOut where AccountNo= @AccountNo)
--print right(@x,len(@x)-1)
set @x = right(@x,len(@x)-1)
return (@x)
end

Go

select distinct TBF.AccountNo, dbo.slash(AccountNo) as Name
from TBFundInOut as TBF
inner join TBClientData as TBC
on TBF.IDNO = TBC.IDNO

--OUTPUT

S-121212 Wong
S-123456 Peter/Chan
S-873645 LEE