Solved

About SQL ( uses sql server 2000)

Posted on 2002-04-02
11
138 Views
Last Modified: 2010-04-04
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 ?



0
Comment
Question by:lingxin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 

Author Comment

by:lingxin
ID: 6916811
????
0
 
LVL 45

Expert Comment

by:aikimark
ID: 6917094
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
 
LVL 10

Expert Comment

by:Jacco
ID: 6917325
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Jacco
ID: 6917326
Oh yeah, just to know is two the maximum number of names combined or can there be more?
0
 

Author Comment

by:lingxin
ID: 6917375
to  Jacco
uses function ?
how writer
0
 
LVL 10

Assisted Solution

by:Jacco
Jacco earned 150 total points
ID: 6919389
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
 
LVL 45

Accepted Solution

by:
aikimark earned 150 total points
ID: 6919472
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
 

Author Comment

by:lingxin
ID: 6919887
go
0
 

Expert Comment

by:delphipal
ID: 6920441
select id,name from table1,table2 where
table1.nameid=table2.nameid order by id
0
 
LVL 4

Expert Comment

by:jsweby
ID: 6920815
Or you can use a join:

SELECT Table1.Name, Table2.ID
FROM Table1
INNER JOIN TABLE2 ON
(Table2.NameID = Table1.NameID)
0
 
LVL 1

Expert Comment

by:pnh73
ID: 9002862
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question