Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

About SQL ( uses sql server 2000)

Posted on 2002-04-02
11
Medium Priority
?
149 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
  • 3
  • 3
  • 2
  • +3
11 Comments
 

Author Comment

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 600 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 46

Accepted Solution

by:
aikimark earned 600 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 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