Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

About SQL ( uses sql server 2000)

Posted on 2002-04-02
11
Medium Priority
?
142 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 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
Industry Leaders: 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 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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

715 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