About SQL ( uses sql server 2000)

table1
NameID  Name
1       aaaa
2       bbbb
3       cccc
4       dddd

table2
ID      NameID
1       1
1       2
2       3
3       4

Query result:
ID     AllName
1      aaaa bbbb
2      cccc
3      dddd
How to write this 'SQL' sentence ?



lingxinAsked:
Who is Participating?
 
aikimarkCommented:
query result tblAllName table:
ID LongInteger Unique
AllName VarChar

Insert Into tblAllName (ID, AllName)
Select table2.ID, table1.Name
From table1 Inner Join table2 on table1.NameID = table2.NameID;

Update tblAllName Inner Join table2 on tblAllName.ID = table2.ID Inner Join table1 on table1.NameID = table2.NameID  
Set AllName = AllName & " " & table1.name
Where table2.NameID Not In
(Select Min(I.NameID)
 From table2 As I
 Where I.ID = tblAllName.ID);
0
 
lingxinAuthor Commented:
????
0
 
aikimarkCommented:
1. Do you need the result in a table or only as a query result?

2. Is the query result concatenation with a space character delimter.

===========================================
The simplest solution produces a table and requires 2 SQL statements.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
JaccoCommented:
You could try having a user defined function for that:

create function dbo.fncAllName(@NameID int)
returns @AllName varchar(256)
as
begin
  .. function code here ..
end

Then the query would be:

select
  ID, dbo.fncAllName(ID)
from
  table2
group by
  ID

I did not implement the function becaus it requires iterating a cursor an I can't remember exactly how to write that.

This solution will be somewhat slow though... Let me know if you want the function.

Regards Jacco

0
 
JaccoCommented:
Oh yeah, just to know is two the maximum number of names combined or can there be more?
0
 
lingxinAuthor Commented:
to  Jacco
uses function ?
how writer
0
 
JaccoCommented:
I will try and make some working code tommorow. But the way you want to do this is not the way to go for a good relational database design. As an exercise in building function with SQL 2000 it is.

Regards Jacco
0
 
lingxinAuthor Commented:
go
0
 
delphipalCommented:
select id,name from table1,table2 where
table1.nameid=table2.nameid order by id
0
 
jswebyCommented:
Or you can use a join:

SELECT Table1.Name, Table2.ID
FROM Table1
INNER JOIN TABLE2 ON
(Table2.NameID = Table1.NameID)
0
 
pnh73Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Split between aikimark and Jacco

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Paul (pnh73)
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.